CODEWITHSUNDEEP
sqlteradata
user3901666
user3901666

Reputation: 409

Not Exists Query with additional filters

I have a query for not exists which doesn't give proper results. However, using the same query with minus it's gives the correct results. Can anyone please tell me where i am getting it wrong. Below are the two codes:

Not exists:

    SEL DISTINCT Accs_Meth_Id FROM X.DIM_LINE  A
WHERE NOT EXISTS 
(
SEL 1 
FROM X.DIM_PRODUCT B 
WHERE A.ACCS_METH_ID=B.ACCS_METH_ID
AND A.STATUS <> 'A'
AND B.STATUS <> 'A'
);

Minus:

SEL DISTINCT ACCS_METH_ID FROM X.DIM_LINE 
WHERE STATUS<>'A'
MINUS
SEL DISTINCT ACCS_METH_ID FROM X.DIM_PRODUCT 
WHERE STATUS<>'A'

Thanks, AMiT

Upvotes: 0

Views: 100

Answers (1)

Tedo G.
Tedo G.

Reputation: 1565

Get the condition A.STATUS <> 'A' out of subquery 

SEL DISTINCT Accs_Meth_Id FROM X.DIM_LINE  A
WHERE 
A.STATUS <> 'A'
AND
NOT EXISTS 
(
SEL 1 
FROM X.DIM_PRODUCT B 
WHERE A.ACCS_METH_ID=B.ACCS_METH_ID    
AND B.STATUS <> 'A'
);

Upvotes: 3

Related Questions