SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with the 'WHERE...IN' command
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Billmiles77
Starting Member

7 Posts

Posted - 11/14/2012 :  10:57:37  Show Profile  Reply with Quote
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
2870 Posts

Posted - 11/14/2012 :  11:07:02  Show Profile  Reply with Quote
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

626 Posts

Posted - 11/14/2012 :  11:15:08  Show Profile  Reply with Quote

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';
Go to Top of Page

Billmiles77
Starting Member

7 Posts

Posted - 11/14/2012 :  13:39:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2870 Posts

Posted - 11/14/2012 :  13:56:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/14/2012 :  14:36:27  Show Profile  Reply with Quote
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 - 11/14/2012 :  16:45:32  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000