SQL IF NOT IN syntax

Question

Can anyone tell me what is wrong with the below code please?

IF (SELECT T0.U_DestType FROM OCRD T0 INNER JOIN ODLN T1 ON T1.CardCode = T0.CardCode WHERE T1.DocEntry ='4') NOT IN ('1','2','6')

BEGIN
SELECT 'SHOW_ERROR' FOR BROWSE
END

It compiles without error but doesn't give the expected result (declare SHOW_ERROR when the U_DestType is anything other than 1,2 or 6)

Many thanks.

TheGameiswar · Answer 1 · 2016-08-19T20:31:57.107

4

use Exists

IF Exists 
 (SELECT 1 FROM OCRD T0 INNER JOIN ODLN T1 ON T1.CardCode = T0.CardCode WHERE T1.DocEntry ='4' and T0.U_DestType NOT IN ('1','2','6'))
Begin
--some quwery
End

There is a Chance that your join might return more than one value and your query will fail.

edited Aug 19 '16 at 20:31

answered Aug 05 '16 at 08:40

TheGameiswar

27,855
8
56
94

1

I think you want `exists` for the OP's logic, but this is definitely a better solution than using `count()`. – Gordon Linoff Aug 05 '16 at 11:08

score 1 · Accepted Answer · answered Aug 05 '16 at 08:42

1

You can also use count function Try this way

IF ((SELECT count(T0.U_DestType) FROM OCRD T0 INNER JOIN ODLN T1 ON T1.CardCode = T0.CardCode WHERE T1.DocEntry ='4') NOT IN ('1','2','6'))==0)

BEGIN
SELECT 'SHOW_ERROR' FOR BROWSE
END

answered Aug 05 '16 at 08:42

Mukesh Kalgude

4,814
2
17
32

SQL IF NOT IN syntax

2 Answers2