CODEWITHSUNDEEP
sqloracle-database
bd528
bd528

Reputation: 886

Oracle combining multiple and & ors

I have the following script :-

SELECT
*
FROM
    view_pending_comm
WHERE
    (ok_to_gen = 'Y' AND comm_type = 'Maya' AND stage <> 3 OR stage <> 6 OR stage <> 9)
OR
    (ok_to_gen = 'Y' AND comm_type = 'Sapphire' AND stage <> 2)

What I need to do is return records where the comm_type is Maya, but not stage 3, 6, or 9 and also where the comm_type is Sapphire, but not stage 2. ok_to_gen needs to be Y in all cases.

I tried the script above, but this also returns records where comm_type is Sapphire and stage is 2.

Upvotes: 0

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Use not in:

SELECT *
FROM  view_pending_comm
WHERE (ok_to_gen = 'Y' AND comm_type = 'Maya' AND stage NOT IN (3, 6, 9)) OR
    (ok_to_gen = 'Y' AND comm_type = 'Sapphire' AND stage <> 2)

The issue is the logic issue of connecting inequalities. The correct logic is:

stage = 3 or stage = 6 or stage = 9

However, the negation uses and:

stage <> 3 and stage <> 6 and stage <> 9

Upvotes: 2

Related Questions