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.
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_namevalue1value2 Not foundvalue3value4 |
|
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?JimEveryday I learn something that somebody else already knew |
|
|
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 FoundFROM ColumnNames N LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON N.CN = C.COLUMN_NAME AND C.TABLE_NAME = 'table_name';[/code] |
|
|
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?JimEveryday I learn something that somebody else already knew
|
|
|
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 thenuse excel to make the code;With Columns as(SELECT columnNamesFROM( VALUES ('a'), -- use excel to put the "('" before each value and "),"('b') -- after each value, except no final "," and copy that here) a (ColumnNames))JimEveryday I learn something that somebody else already knew |
|
|
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. |
|
|
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_noFROM cust_table ct WHERE ct.acct_no IN (123, 456, 789, 923, 567, 891);to;With Columns as(SELECT columnNamesFROM( 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 thenuse excel to make the code;With Columns as(SELECT columnNamesFROM( VALUES ('a'), -- use excel to put the "('" before each value and "),"('b') -- after each value, except no final "," and copy that here) a (ColumnNames))JimEveryday I learn something that somebody else already knew
|
|
|
|
|
|
|
|