Headache of csv values and subselects

Question

Headache of csv values and subselects

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

joe.eager Ten Centuries Points: 1008 More actions · Answer 1

Lynn Pettis (7/5/2012)
Note, you may get duplicate rows of data if there are multiple matches, so I'm sure that there may be other ways to accomplish this. I am looking at another way as we speak.

Thank you, I'll be digesting the approach above to get a good understanding of it.

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

And I found it. Try this in place of my original code above.

DECLARE @StateFipsCSV varchar(500) = '01,03'

SELECT

*

FROM

[dbo].[ProductInventory] prdinv

WHERE

exists (SELECT instr.Item FROM dbo.DelimitedSplit8K(@StateFipsCSV,',') instr INNER JOIN

dbo.DelimitedSplit8K(prdinv.StateFips,',') dtstr ON instr.Item = dtstr.Item);

GO

joe.eager Ten Centuries Points: 1008 More actions · Answer 3

Lynn Pettis (7/5/2012)
And I found it. Try this in place of my original code above.
DECLARE @StateFipsCSV varchar(500) = '01,03'
SELECT
*
FROM
[dbo].[ProductInventory] prdinv
WHERE
exists (SELECT instr.Item FROM dbo.DelimitedSplit8K(@StateFipsCSV,',') instr INNER JOIN
dbo.DelimitedSplit8K(prdinv.StateFips,',') dtstr ON instr.Item = dtstr.Item);
GO

Wow. thank you so much that helped a lot. I didnt think of using an Inner Join in there. :blush:

Now I have to figure out how to translate an empty string into all values. :w00t:

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

joe.eager (7/5/2012)
Lynn Pettis (7/5/2012)
And I found it. Try this in place of my original code above.
DECLARE @StateFipsCSV varchar(500) = '01,03'
SELECT
*
FROM
[dbo].[ProductInventory] prdinv
WHERE
exists (SELECT instr.Item FROM dbo.DelimitedSplit8K(@StateFipsCSV,',') instr INNER JOIN
dbo.DelimitedSplit8K(prdinv.StateFips,',') dtstr ON instr.Item = dtstr.Item);
GO
Wow. thank you so much that helped a lot. I didnt think of using an Inner Join in there. :blush:
Now I have to figure out how to translate an empty string into all values. :w00t:

If you meant that @StateFipsCSV = '', that's easy even though there are thoughs that would disagree.

DECLARE @StateFipsCSV varchar(500) = '01,03'

SELECT

*

FROM

[dbo].[ProductInventory] prdinv

WHERE

exists (SELECT instr.Item FROM dbo.DelimitedSplit8K(@StateFipsCSV,',') instr INNER JOIN

dbo.DelimitedSplit8K(prdinv.StateFips,',') dtstr ON instr.Item = dtstr.Item)

OR @StateFipsCSV = '';

GO

joe.eager Ten Centuries Points: 1008 More actions · Answer 5

Lynn Pettis (7/5/2012)
joe.eager (7/5/2012)
Lynn Pettis (7/5/2012)
And I found it. Try this in place of my original code above.
DECLARE @StateFipsCSV varchar(500) = '01,03'
SELECT
*
FROM
[dbo].[ProductInventory] prdinv
WHERE
exists (SELECT instr.Item FROM dbo.DelimitedSplit8K(@StateFipsCSV,',') instr INNER JOIN
dbo.DelimitedSplit8K(prdinv.StateFips,',') dtstr ON instr.Item = dtstr.Item);
GO
Wow. thank you so much that helped a lot. I didnt think of using an Inner Join in there. :blush:
Now I have to figure out how to translate an empty string into all values. :w00t:
If you meant that @StateFipsCSV = '', that's easy even though there are thoughs that would disagree.
DECLARE @StateFipsCSV varchar(500) = '01,03'
SELECT
*
FROM
[dbo].[ProductInventory] prdinv
WHERE
exists (SELECT instr.Item FROM dbo.DelimitedSplit8K(@StateFipsCSV,',') instr INNER JOIN
dbo.DelimitedSplit8K(prdinv.StateFips,',') dtstr ON instr.Item = dtstr.Item)
OR @StateFipsCSV = '';
GO

Yup Just typed that into management studio and then saw my notification email from this.

Thank you so much for all your help!