| Author |
 Topic  |
|
|
Billmiles77
Starting Member
7 Posts |
 Posted - 11/14/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/14/2012 : 11:07:02
|
Where do the values in your IN clause come from?
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
Ifor
Constraint Violating Yak Guru
476 Posts |
Posted - 11/14/2012 : 11:15:08
|
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';
|
|
|
|
Billmiles77
Starting Member
7 Posts |
Posted - 11/14/2012 : 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
|
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/14/2012 : 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 |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3832 Posts |
Posted - 11/14/2012 : 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 - 11/14/2012 : 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
|
|
|
| |
Topic |
|
Help with the 'WHERE...IN' command
