CODEWITHSUNDEEP
sql
Kiran Rao
Kiran Rao

Reputation: 3

return only false values in SQL

How do I modify this to return only false values and avoid true values

SELECT b."/BIC/ZSORHDR",
case when a."/BIC/ZSORHDR" is null then 'false' else end
FROM sappha."/BIC/ADD060400"  b
LEFT OUTER JOIN sappha."/BIC/AOPM20400" a
ON (b."/BIC/ZSORHDR" = a."/BIC/ZSORHDR")
WHERE  b."LOAD_DATE" = '20210118'

Upvotes: 0

Views: 34

Answers (2)

Popeye
Popeye

Reputation: 35910

the best way is to use NOT EXISTS as follows:

SELECT b."/BIC/ZSORHDR"
  FROM sappha."/BIC/ADD060400"  b
WHERE NOT EXISTS 
      (select 1 from sappha."/BIC/AOPM20400" a
        WHERE b."/BIC/ZSORHDR" = a."/BIC/ZSORHDR")
  AND b."LOAD_DATE" = '20210118'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

Move the logic in the CASE expression in the select to the WHERE clause:

SELECT b."/BIC/ZSORHDR"
FROM sappha."/BIC/ADD060400" b
LEFT JOIN sappha."/BIC/AOPM20400" a
    ON b."/BIC/ZSORHDR" = a."/BIC/ZSORHDR"
WHERE
    b."LOAD_DATE" = '20210118' AND
    a."/BIC/ZSORHDR" IS NULL;

Upvotes: 1

Related Questions