CODEWITHSUNDEEP
sqloracle-databasejoinselectwhere-clause
Slowalesce
Slowalesce

Reputation: 13

Oracle SQL - WHERE Clause



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. :)

Upvotes: 0

Views: 40

Answers (1)

GMB
GMB

Reputation: 222492

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')

Upvotes: 2

Related Questions