SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


WHERE Filter


WHERE Filter

Author
Message
2Tall
2Tall
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3018 Visits: 1203
Hi to all. I haven't posted for a while and it is now time to wear my sql cap again Smile

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

Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12050 Visits: 5478
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 Smile

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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
2Tall
2Tall
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3018 Visits: 1203
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

Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12050 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
2Tall
2Tall
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3018 Visits: 1203
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 Smile

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

Steven Willis
Steven Willis
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1963 Visits: 1721
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



2Tall
2Tall
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3018 Visits: 1203
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 Smile
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

Steven Willis
Steven Willis
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1963 Visits: 1721
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.

 
2Tall
2Tall
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3018 Visits: 1203
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search