Link to home
Get AccessLog in
Avatar of anushahanna
anushahannaFlag for United States of America

asked on

where not in ( int value and null)

statusfk is supposed to be null or have an int value that is 8 numbers. but i saw in the table there is one with 7 digits.

but the following query
select * from tblsinger where len(statusfk) not in (8, null)
did not bring back the records for statusfk with 7 length.

what is wrong with the query?

thanks
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of anushahanna

ASKER

>>NOT IN + NULL = bad query
>>you can only test a column against null with the IS NULL/IS NOT NULL SYNTAX

THANK YOU....

now,
select * from tblsinger where statusfk is not null and len(statusfk)<8
worked
(len does work for int - can you please test that)
SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
thanks for your helpful explanation.