Oracle SQL - WHERE Clause

Question

I'm new here so forgive me if this could be worded better.

I need to return details for any people who are either:

Grade 'C' and salary not equal to 9.00
or
Grade 'D' and salary not equals 9.75

E.g.

SELECT

paam.ASSIGNMENT_NUMBER,
cs.SALARY_AMOUNT,
pgf.NAME Grade

FROM

CMP_SALARY cs,
PER_ALL_ASSIGNMENTS_M paam,
PER_GRADES_F_TL pgf

WHERE 

        pgf.GRADE_ID = paam.GRADE_ID
AND     cs.ASSIGNMENT_ID = paam.ASSIGNMENT_ID
AND     (pgf.name = 'C' AND cs.SALARY_AMOUNT <> '9.00')
                or (pgf.name = 'D' AND cs.SALARY_AMOUNT <> '9.75')

Any help you can give will be much appreciated. :)

Tip of today: Always use modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! — jarlh, Jun 10 '20 at 10:13
Your logic looks correct (minus the comment by @jarlh above), other than maybe the salary amount should be compared to numbers, not string literals. What is wrong with your current query? — Tim Biegeleisen, Jun 10 '20 at 10:17
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Show what relevant part you can do & explain re being stuck. Find any 1st error & say what you expected & why. — philipxy, Jun 10 '20 at 10:39
If you found your first wrong subexpression & chopped down your code you would find this is a faq. [SQL Logic Operator Precedence: And and Or](https://stackoverflow.com/q/1241142/3404097) — philipxy, Jun 10 '20 at 10:43

score 2 · Accepted Answer · answered Jun 10 '20 at 10:18

The problem is logical prescendence. or has lower priority than and, so you need to surround the entire or conditions with parentheses.

So basically change this:

WHERE
    pgf.GRADE_ID = paam.GRADE_ID
    AND cs.ASSIGNMENT_ID = paam.ASSIGNMENT_ID
    AND (pgf.name = 'C' AND cs.SALARY_AMOUNT <> '9.00')
    OR (pgf.name = 'D' AND cs.SALARY_AMOUNT <> '9.75')

To:

WHERE
    pgf.GRADE_ID = paam.GRADE_ID
    AND cs.ASSIGNMENT_ID = paam.ASSIGNMENT_ID
    AND (
        (pgf.name = 'C' AND cs.SALARY_AMOUNT <> '9.00')
        OR (pgf.name = 'D' AND cs.SALARY_AMOUNT <> '9.75')
    )

The inner parentheses are not strictly needed (for the afformentioned reasons), but they make the intent clearer.

I would also recommend rewriting the query to use standard, explicit joins, rather than old-school, implicit joins. Incidently, this takes away the prescendence issue:

SELECT
    paam.ASSIGNMENT_NUMBER,
    cs.SALARY_AMOUNT,
    pgf.NAME Grade
FROM CMP_SALARY cs
INNER JOIN PER_ALL_ASSIGNMENTS_M paam 
    ON cs.ASSIGNMENT_ID = paam.ASSIGNMENT_ID
INNER JOIN PER_GRADES_F_TL pgf
    ON pgf.GRADE_ID = paam.GRADE_ID
WHERE 
    (pgf.name = 'C' AND cs.SALARY_AMOUNT <> '9.00')
    OR (pgf.name = 'D' AND cs.SALARY_AMOUNT <> '9.75')

Oracle SQL - WHERE Clause

1 Answers1