IF statement or Case

Transact-SQL
1

I have the following example.

Doc_NO  Ref1    Ref 2    Ref3
123      TAR    Inv      NULL
456      TAR    NULL     NULL
789      Inv    NULL     TAR

I need to check all three columns. If a doc_no has TAR in one column and null in the other two, I need it returned in my script result. In the case above, the script should only return Doc_no 456, as it is the only one that has TAR in one of the Ref columns and NULL in the remaining two.

select doc_no
from table1
where (ref1 = 'TAR' and ref2 is null and ref3 is null) or (ref1 is null and ref2 = 'TAR' and ref3 is null) or (ref1 is null and ref2 is null and ref3 = 'TAR')

Will this script I wrote work correctly?

2

I think so. Or this:

SELECT doc_no
FROM table1
WHERE 'TAR' IN (Ref1, Ref2, Ref3) AND 
    CASE WHEN Ref1 IS NULL THEN 1 ELSE 0 END +    
    CASE WHEN Ref2 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN Ref3 IS NULL THEN 1 ELSE 0 END = 2
1 Like
3 
DECLARE @t table(Doc_NO char(3), Ref1 char(3), Ref2 char(3), Ref3 char(3));
INSERT @t
   (  Doc_NO
    , Ref1
    , Ref2
    , Ref3
   )
VALUES
   ( '123', 'TAR', 'Inv', NULL )
 , ( '456', 'TAR', NULL, NULL )
 , ( '789', 'Inv', NULL, 'TAR' );
DECLARE @util table(Txt char(3) NULL, Val int);
INSERT @util( Txt, Val )
VALUES
   ( 'TAR', 1 )
 , ( 'Inv', 2 )
 , ( NULL, 0 )
SELECT t.Doc_NO
     , t.Ref1
     , t.Ref2
     , t.Ref3
FROM @t t
JOIN @util u1 ON IsNull(t.Ref1, '') = IsNull(u1.Txt, '')
JOIN @util u2 ON IsNull(t.Ref2, '') = IsNull(u2.Txt, '') 
JOIN @util u3 ON IsNull(t.Ref3, '') = IsNull(u3.Txt, '')
AND u1.Val+u2.Val+u3.Val=1;

image

1 Like
4

Would this be any good?

WHERE COALESCE(Ref1, '')
    + COALESCE(Ref2, '')
    + COALESCE(Ref3, '')
    = 'TAR'
1 Like
5

Thank you all for your help.