Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with the 'WHERE...IN' command

Author  Topic 

Billmiles77
Starting Member

7 Posts

Posted - 2012-11-14 : 10:57:37
Can anyone tell me how to display any values not found when using the 'WHERE'/'IN' command without using a temporary variable or table? So if value2, in below example, is not found in the table can I display value2, with a not found indicator, while also displaying all the values that were found?

SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', 'value3','value4',...)

result:
column_name

value1
value2 Not found
value3
value4

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-14 : 11:07:02
Where do the values in your IN clause come from?

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-11-14 : 11:15:08
[code]
WITH ColumnNames(CN)
AS
(
SELECT N'value1'
UNION ALL SELECT N'value2'
UNION ALL SELECT N'value3'
UNION ALL SELECT N'value4'
-- ...
)
SELECT N.CN
,CASE WHEN C.COLUMN_NAME IS NULL THEN 'No' ELSE 'Yes' END AS Found
FROM ColumnNames N
LEFT JOIN INFORMATION_SCHEMA.COLUMNS C
ON N.CN = C.COLUMN_NAME
AND C.TABLE_NAME = 'table_name';
[/code]
Go to Top of Page

Billmiles77
Starting Member

7 Posts

Posted - 2012-11-14 : 13:39:37
The values are supplied by the user. This is actually a list of say 2000 account numbers in txt format that I cut-n-paste into the query in statement. So I am looking for a way to program a check for these values without the addition of a bunch of unions.

Thanks for looking at this and taking the time.

quote:
Originally posted by jimf

Where do the values in your IN clause come from?

Jim

Everyday I learn something that somebody else already knew

Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-14 : 13:56:41
If you could import that text file into sql that would sure make things easier. But if you can't, Ifor's suggestion is the way to go. But instead of using unions, I'd copy your text file in to Excel, and then
use excel to make the code

;With Columns as
(

SELECT columnNames
FROM

(

VALUES
('a'), -- use excel to put the "('" before each value and "),"
('b') -- after each value, except no final ","
and copy that here

) a (ColumnNames)

)


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-14 : 14:36:27
If you have a delimited string, you might also consider a parse/split function to populate a table and then use that to join to.
Go to Top of Page

Billmiles77
Starting Member

7 Posts

Posted - 2012-11-14 : 16:45:32
Thank you for the reply. However I am having difficulty coding. I am starting with this:

SELECT ct.acct_no
FROM cust_table ct
WHERE ct.acct_no IN (123, 456, 789, 923, 567, 891);

to
;With Columns as
(

SELECT columnNames
FROM

(

VALUES
('a'), -- use excel to put the "('" before each value and "),"
('b') -- after each value, except no final ","
and copy that here

) a (ColumnNames)

)

I am following some but could you break it down a little more? I am thinking "Columns" is whatever I define but not too clear what "columnNames" represents. I tried the other version and I get an error stating it was expecting "FROM" on the "ALL" parm. Again I appreciate the insight.


quote:
Originally posted by jimf

If you could import that text file into sql that would sure make things easier. But if you can't, Ifor's suggestion is the way to go. But instead of using unions, I'd copy your text file in to Excel, and then
use excel to make the code

;With Columns as
(

SELECT columnNames
FROM

(

VALUES
('a'), -- use excel to put the "('" before each value and "),"
('b') -- after each value, except no final ","
and copy that here

) a (ColumnNames)

)


Jim

Everyday I learn something that somebody else already knew

Go to Top of Page
   

- Advertisement -