1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

no syntax error found but query shows restricted results

Discussion in 'SQL PL/SQL' started by nerd_buzz, May 13, 2013.

  1. nerd_buzz

    nerd_buzz Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    The problem and my answer as pasted as pictures. i dont know why the result for salary = 7000 is showing when i specifically wrote salary <> 7000. please help me the other conditions seem to be working just fine.
     

    Attached Files:

  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi dude

    Because of 'OR' condition you are getting the result like this. So use the below condition.

    WHERE ( job_id like 'SA_REP'
    OR job_id like 'ST_CLERK'
    )

    -- Here remaining condition as it is


    Regards
    Sambasiva Reddy
     
  3. nerd_buzz

    nerd_buzz Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Thank you so much MR. Reddy. that makes tons of sense. A fellow indian helping another fellow indian. Thank you again

    Regards K. Chawla
     
  4. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    :D Don't u worry, Don't u worry child ..... We're always there 4 u :hurray

    P.S: I'm not an uncle, the lyrics goes that way ;)
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It would have been nice to explain WHY the lone OR condition returned the incorrect results. When writing a WHERE clause the OR condition will start its own set of criteria, that is to say a where clause like this:

    Code (SQL):

    WHERE col1 = 'A'
    OR col2 = 'B'
    AND col3 = 14
     
    ONLY associates the additional col3 condition to the OR part of the clause. Looking at your original query (rewritten to use = instead of the misused LIKE):

    Code (SQL):
    SQL> SELECT last_name, job_id, salary
      2  FROM employees
      3  WHERE job_id = 'SA_REP'
      4  OR job_id = 'ST_CLERK'
      5  AND salary <> 2500
      6  AND salary <> 3500
      7  AND salary <> 7000
      8  ORDER BY salary DESC;
     
    LAST_NAME                 JOB_ID         SALARY
    ------------------------- ---------- ----------
    Ozer                      SA_REP          11500
    Abel                      SA_REP          11000
    Vishney                   SA_REP          10500
    King                      SA_REP          10000
    Bloom                     SA_REP          10000
    Tucker                    SA_REP          10000
    Fox                       SA_REP           9600
    Bernstein                 SA_REP           9500
    Sully                     SA_REP           9500
    Greene                    SA_REP           9500
    Hall                      SA_REP           9000
    McEwen                    SA_REP           9000
    Hutton                    SA_REP           8800
    Taylor                    SA_REP           8600
    Livingston                SA_REP           8400
    Olsen                     SA_REP           8000
    Smith                     SA_REP           8000
    Doran                     SA_REP           7500
    Cambrault                 SA_REP           7500
    Smith                     SA_REP           7400
    Bates                     SA_REP           7300
    Marvins                   SA_REP           7200
    Tuvault                   SA_REP           7000
    GRANT                     SA_REP           7000
    Sewall                    SA_REP           7000
    Lee                       SA_REP           6800
    Ande                      SA_REP           6400
    Johnson                   SA_REP           6200
    Banda                     SA_REP           6200
    Kumar                     SA_REP           6100
    Ladwig                    ST_CLERK         3600
    Bissot                    ST_CLERK         3300
    Mallin                    ST_CLERK         3300
    Stiles                    ST_CLERK         3200
    Nayer                     ST_CLERK         3200
    Davies                    ST_CLERK         3100
    Rogers                    ST_CLERK         2900
    Atkinson                  ST_CLERK         2800
    Seo                       ST_CLERK         2700
    Mikkilineni               ST_CLERK         2700
    Matos                     ST_CLERK         2600
    Gee                       ST_CLERK         2400
    Landry                    ST_CLERK         2400
    Philtanker                ST_CLERK         2200
    Markle                    ST_CLERK         2200
    Olson                     ST_CLERK         2100
     
    46 ROWS selected.
     
    SQL>
    what you have asked for from the database is all records where the job_id = 'SA_REP' regardless of their salary and all records where the job_id is 'ST_CLERK' and the salary is not in those you have listed. As mentioned in a prior response you need to paranthesise the OR condition:

    Code (SQL):
    SQL> SELECT last_name, job_id, salary
      2 FROM employees
      3 WHERE (job_id = 'SA_REP'
      4 OR job_id = 'ST_CLERK')
      5 AND salary <> 2500
      6 AND salary <> 3500
      7 AND salary <> 7000
      8 ORDER BY salary DESC
      9 /
     
    LAST_NAME                 JOB_ID         SALARY
    ------------------------- ---------- ----------
    Ozer                      SA_REP          11500
    Abel                      SA_REP          11000
    Vishney                   SA_REP          10500
    King                      SA_REP          10000
    Bloom                     SA_REP          10000
    Tucker                    SA_REP          10000
    Fox                       SA_REP           9600
    Greene                    SA_REP           9500
    Bernstein                 SA_REP           9500
    Sully                     SA_REP           9500
    Hall                      SA_REP           9000
    McEwen                    SA_REP           9000
    Hutton                    SA_REP           8800
    Taylor                    SA_REP           8600
    Livingston                SA_REP           8400
    Olsen                     SA_REP           8000
    Smith                     SA_REP           8000
    Doran                     SA_REP           7500
    Cambrault                 SA_REP           7500
    Smith                     SA_REP           7400
    Bates                     SA_REP           7300
    Marvins                   SA_REP           7200
    Lee                       SA_REP           6800
    Ande                      SA_REP           6400
    Johnson                   SA_REP           6200
    Banda                     SA_REP           6200
    Kumar                     SA_REP           6100
    Ladwig                    ST_CLERK         3600
    Mallin                    ST_CLERK         3300
    Bissot                    ST_CLERK         3300
    Nayer                     ST_CLERK         3200
    Stiles                    ST_CLERK         3200
    Davies                    ST_CLERK         3100
    Rogers                    ST_CLERK         2900
    Atkinson                  ST_CLERK         2800
    Seo                       ST_CLERK         2700
    Mikkilineni               ST_CLERK         2700
    Matos                     ST_CLERK         2600
    Gee                       ST_CLERK         2400
    Landry                    ST_CLERK         2400
    Philtanker                ST_CLERK         2200
    Markle                    ST_CLERK         2200
    Olson                     ST_CLERK         2100
     
    43 ROWS selected.
     
    SQL>
    Notice that there are three fewer rows to the result set, the rows where salary equalled 7000. What the above query has asked Oracle for is:

    Return all rows where the job_id is either 'SA_REP' or 'ST_CLERK' and the salary is not in the values 2500, 3500 and 7000. The series of <> conditions can be replaced with a single statement as shown below:

    Code (SQL):
    SQL> SELECT last_name, job_id, salary
      2  FROM employees
      3  WHERE (job_id = 'SA_REP'
      4  OR job_id = 'ST_CLERK')
      5  AND salary NOT IN  (2500,3500,7000)
      6  ORDER BY salary DESC
      7  /
     
    LAST_NAME                 JOB_ID         SALARY
    ------------------------- ---------- ----------
    Ozer                      SA_REP          11500
    Abel                      SA_REP          11000
    Vishney                   SA_REP          10500
    King                      SA_REP          10000
    Bloom                     SA_REP          10000
    Tucker                    SA_REP          10000
    Fox                       SA_REP           9600
    Greene                    SA_REP           9500
    Bernstein                 SA_REP           9500
    Sully                     SA_REP           9500
    Hall                      SA_REP           9000
    McEwen                    SA_REP           9000
    Hutton                    SA_REP           8800
    Taylor                    SA_REP           8600
    Livingston                SA_REP           8400
    Olsen                     SA_REP           8000
    Smith                     SA_REP           8000
    Doran                     SA_REP           7500
    Cambrault                 SA_REP           7500
    Smith                     SA_REP           7400
    Bates                     SA_REP           7300
    Marvins                   SA_REP           7200
    Lee                       SA_REP           6800
    Ande                      SA_REP           6400
    Johnson                   SA_REP           6200
    Banda                     SA_REP           6200
    Kumar                     SA_REP           6100
    Ladwig                    ST_CLERK         3600
    Mallin                    ST_CLERK         3300
    Bissot                    ST_CLERK         3300
    Nayer                     ST_CLERK         3200
    Stiles                    ST_CLERK         3200
    Davies                    ST_CLERK         3100
    Rogers                    ST_CLERK         2900
    Atkinson                  ST_CLERK         2800
    Seo                       ST_CLERK         2700
    Mikkilineni               ST_CLERK         2700
    Matos                     ST_CLERK         2600
    Gee                       ST_CLERK         2400
    Landry                    ST_CLERK         2400
    Philtanker                ST_CLERK         2200
    Markle                    ST_CLERK         2200
    Olson                     ST_CLERK         2100
     
    43 ROWS selected.
     
    SQL>
     
    It's the same conditions you're looking for but the statement is easier to read and understand.
     
    nerd_buzz likes this.
  6. nerd_buzz

    nerd_buzz Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Thank you so much David that was a wonderful explanation. Thankyou for taking out precious time out of ur schedule to school me. The IN condition makes the code much cleaner. Thanks again!!!