Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

WHERE Filter Expand / Collapse
Author
Message
Posted Tuesday, November 13, 2012 5:09 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
Hi to all. I haven't posted for a while and it is now time to wear my sql cap again :)

I have a query which in brief has the following filter:


WHERE ProductId = @ProductId AND StructureVersion = @StructureVersion AND Type IN (@Types)
AND ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq AND ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc


The bit I am interested in is:

AND ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq AND ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc


The filter currenty returns 1 row as both @ProdFolderReq and @ProdAidDoc = 1

I would like to return 1 record if either or both return 1 (i.e. drop the AND). I tried OR but this returns multiple records.

Can this be done?
Kind Regards,
Phil.



-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper
Post #1384036
Posted Tuesday, November 13, 2012 6:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 2,873, Visits: 5,184
2Tall (11/13/2012)
Hi to all. I haven't posted for a while and it is now time to wear my sql cap again :)

I have a query which in brief has the following filter:


WHERE ProductId = @ProductId AND StructureVersion = @StructureVersion AND Type IN (@Types)
AND ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq AND ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc


The bit I am interested in is:

AND ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq AND ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc


The filter currenty returns 1 row as both @ProdFolderReq and @ProdAidDoc = 1

I would like to return 1 record if either or both return 1 (i.e. drop the AND). I tried OR but this returns multiple records.

Can this be done?
Kind Regards,
Phil.



1st. What you are expecting to have in @Types? Is it comma separated string? If yes, then your "Type IN (@Types)" filter will never work as it is absolutely the same as "Type = @Types"

2nd. You should really show what you have tried with OR.
But I guess it should be like that :

AND 
( ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq
OR ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc )



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1384063
Posted Tuesday, November 13, 2012 7:21 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
Hi. I have managed to get it working. The query has been written for a report.

Let me explain. I was never able to workout how to pass values as a comma separated string so I have a fudge. I have a number of datasets that allow multi-value selection, this is where the IN (@Paremeter) comes in. Example:


SELECT DISTINCT Type,
CASE
WHEN Type = 'T' THEN 'A'
WHEN Type = 'P' THEN 'B'
WHEN Type = 'M' THEN 'C'
WHEN Type = 'B' THEN 'D'
END AS TypeDescription
FROM MyTable


The report parameter allows multi-value selection and values are derived from a query.

So for my boolean values I have datasets that returns both true and false. The report parameters default to 'True & False'. So the AND clause remains. The user can select 'True' for one parameter then 'False' for the other (or what ever combination they want).

I hope that makes sense!
Phil.


-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper
Post #1384081
Posted Tuesday, November 13, 2012 7:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 2,873, Visits: 5,184
To pass values as a comma separated string, you need to concatenate them into one comma separated string and pass it.
To use it in you procedure, you can use "IN" clause with Dynamic-SQL, or use some string splitter functions (you can find good one on this forum) to split into table.

And as I said before, "IN (@Paremeter)" is exactly the same as "= @Paremeter", and it is irrelevant of what you have inside of @Paremeter.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1384102
Posted Tuesday, November 13, 2012 7:54 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
Thanks for posting Eugene. The approach I have become used to implementing seems to work. I did search the forum many moons ago regards the approach you recommend but my grey matter couldn't get a handle on how to implement it :)

In the appoach I have adopted the parameters list a set of values returned by the underlying query (that I want the user to see). They can select 1 or all values. So for instance if a product came in varying colours I might have a query that returned red, blue, yellow. The parameter uses this query and displays values as available selections. The user can select red,blue and yellow or just red. Data returned is filtered by their selection. I'm not trying to teach you how to suck eggs by the way, just explaining it as I see it! I am but a mere novice when it comes to the black art of writing code...........
Thnaks,
Phil.


-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper
Post #1384110
Posted Wednesday, November 14, 2012 12:40 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Use Jeff Moden's splitter function (copied below for convenience).


/* Sample Data */

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[Type] CHAR(1) NULL,
[TypeDesc] CHAR(1) NULL,
PRIMARY KEY (ID),
UNIQUE (ID))

INSERT INTO #TempTable
SELECT 'T','A'
UNION
SELECT 'P','B'
UNION
SELECT 'M','C'
UNION
SELECT 'B','D'
UNION
SELECT 'X','X'
UNION
SELECT 'Y','Y'
UNION
SELECT 'Z','Z'

/* Display sample data */
SELECT
[Type]
,[TypeDesc]
FROM
#TempTable


/* Show just the rows that match the delimited string */
DECLARE @strTypes VARCHAR(50)
SET @strTypes = 'T,P,M,B'

SELECT DISTINCT
[Type]
,[TypeDesc]
FROM
#TempTable AS tt
CROSS APPLY
dbo.tvfDelimitedSplit8K(@strTypes,',') AS ds
WHERE
tt.[Type] = ds.Item



CREATE FUNCTION [dbo].[tvfDelimitedSplit8K]
(
@pString VARCHAR(8000)
,@pDelimiter CHAR(1)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN

WITH E1(N)
AS (
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
), --10E+1 or 10 rows
E2(N)
AS (
SELECT
1
FROM
E1 a
,E1 b
), --10E+2 or 100 rows
E4(N)
AS (
SELECT
1
FROM
E2 a
,E2 b
), --10E+4 or 10,000 rows max
cteTally(N)
AS (
SELECT TOP (ISNULL(DATALENGTH(@pString),0))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E4
),
cteStart(N1)
AS (
SELECT
1
UNION ALL
SELECT
t.N + 1
FROM
cteTally t
WHERE
SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1)
AS (
SELECT
s.N1
,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0) - s.N1,8000)
FROM
cteStart s
)

SELECT
ItemNumber = ROW_NUMBER() OVER (ORDER BY l.N1)
,Item = SUBSTRING(@pString,l.N1,l.L1)
FROM
cteLen l ;
GO


Post #1384814
Posted Thursday, November 15, 2012 9:04 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
Hi Steven. I read 'Jeff's' post after Eugene's replied (I did a search of the forum). To be honest I have no idea how to implement. I never took to programming so when people start talking functions my brain switches off........

Feel free to educate me with an example, you never know, the penny might just drop!

At the moment I have a query that I use in a report data set. When users want to select multiple values I provide them with another data set to select values from using IN.

How would I replace with a function, it all seems complicated :)
Thanks,
Phil.


-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper
Post #1385228
Posted Thursday, November 15, 2012 10:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Create a stored procedure like this (I will assume you have already created the splitter function):


CREATE PROCEDURE dbo.GetTypeDesc
@strTypes VARCHAR(255)
,@strDelimiter CHAR(1)
AS
BEGIN

SELECT DISTINCT
[Type]
,[TypeDesc]
FROM
#TempTable AS tt
CROSS APPLY
dbo.tvfDelimitedSplit8K(@strTypes,@strDelimiter) AS ds
WHERE
tt.[Type] = ds.Item

END


Then call the procedure by passing in your delimited string as the input parameter. Of course, instead of a temp table as used here for demo purposes you would probably have a more complex query against actual tables.


IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[Type] CHAR(1) NULL,
[TypeDesc] CHAR(1) NULL,
PRIMARY KEY (ID),
UNIQUE (ID))

INSERT INTO #TempTable
SELECT 'T','A'
UNION
SELECT 'P','B'
UNION
SELECT 'M','C'
UNION
SELECT 'B','D'
UNION
SELECT 'X','X'
UNION
SELECT 'Y','Y'
UNION
SELECT 'Z','Z'

/* Display sample data */
SELECT
[Type]
,[TypeDesc]
FROM
#TempTable

/* Display the filtered data */
EXEC dbo.GetTypeDesc 'T,P,M,B',',' -- comma delimited
--or
EXEC dbo.GetTypeDesc 'T|P|M|B','|' -- pipe delimited


The Splitter Function is a TABLE-VALUED FUNCTION. So the output of that function is a TABLE just like a view or temp table and thus you can use the output to join to any other table. This turns the entire query into a set-based query that doesn't require additional looping once the function has parsed the delimited string into individual rows.

The input string 'T,P,M,B' could be the output from an HTML dropdown, for example, and then the keys represented in the delimited string are used to cross-filter a query to show only the related rows.

 
Post #1385260
Posted Sunday, November 18, 2012 7:49 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
Thanks Steven. I will have a play. Great stuff.
Many Thanks,
Phil.


-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper
Post #1386045
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse