November 13, 2012 at 5:09 am
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
November 13, 2012 at 6:58 am
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 )
November 13, 2012 at 7:21 am
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
November 13, 2012 at 7:43 am
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.
November 13, 2012 at 7:54 am
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
November 14, 2012 at 12:40 pm
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
November 15, 2012 at 9:04 am
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
November 15, 2012 at 10:07 am
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.
November 18, 2012 at 7:49 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply