John Cobby John Cobby - 4 months ago 7
SQL Question

SQL IF NOT IN syntax

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.

Answer

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