erfan erfan - 3 months ago 8
SQL Question

Unrelated result in the output?

this is my query:

SELECT TOP 10000 [Service_order]
,[COMPANY]
,[PENDING_DAYS]
,[SERVICE_TYPE]
,[SERVICE_TYPE_TXT]
,[STATUS]
,[STATUS_TEXT]
,[REASON]
,[REASON_TEXT]
,[ASC code]
,[ASC name]
,[MODEL]
,[INOUTWTY]
,[Part_code1]
,[PS1]
,[confirmation_No1]
,[Part_code2]
,[PS2]
,[SO_NO2]
,[Part_code3]
,[PS3]
,[SO_NO3]
,[Part_code4]
,[PS4]
,[SO_NO4]
,[Part_code5]
,[PS5]
,[SO_NO5]
,[Engineer name]
FROM ['NewLP']
where REASON_TEXT = 'Parts Not available (ASC)'
or REASON_TEXT = 'Parts In Transit (Samsung)'
or REASON_TEXT = 'Parts Back Ordered (Samsung)'
and PS1 = 'U'
and PS2 = 'U' or PS2 = ''
and PS3 = 'U' or PS3 = ''
and PS4 = 'U' or PS4 = ''
and PS5 = 'U' or PS5 = ''


I don't know which part is wrong exactly but the reason_text should be those in the condition and first ps should be 'U' and the rest should be 'U' or empty, but when I run the query the result is not what it should be.
here is the results:

enter image description here

And when I use one of the reason_text in the condition only first two PS's show correctly, the rest(PS3,PS4,PS5) is not following the condition?

here is the result when I use one of the reason_Text, I only selected the columns that are important.
enter image description here

I would appreciate any help.

Answer

You need to put your PS* and REASON TEXT where using OR conditions into brackets like this:

where (
      REASON_TEXT = 'Parts Not available (ASC)'
or    REASON_TEXT = 'Parts In Transit (Samsung)'
or    REASON_TEXT = 'Parts Back Ordered (Samsung)'
      )
and   PS1 = 'U' 
and   ( PS2 = 'U' or PS2 = '' )
and   ( PS3 = 'U' or PS3 = '' )
and   ( PS4 = 'U' or PS4 = '' )
and   ( PS5 = 'U' or PS5 = '' )

Remember that AND operator has precedence over OR and when combining these conditions, it is important to use parentheses so that the database knows in which order to evaluate each condition.

Full query

SELECT TOP 10000 [Service_order]
      ,[COMPANY]
      ,[PENDING_DAYS]
      ,[SERVICE_TYPE]
      ,[SERVICE_TYPE_TXT]
      ,[STATUS]
      ,[STATUS_TEXT]
      ,[REASON]
      ,[REASON_TEXT]
      ,[ASC code]
      ,[ASC name]
      ,[MODEL]
      ,[INOUTWTY]
      ,[Part_code1]
      ,[PS1]
      ,[confirmation_No1]
      ,[Part_code2]
      ,[PS2]
      ,[SO_NO2]
      ,[Part_code3]
      ,[PS3]
      ,[SO_NO3]
      ,[Part_code4]
      ,[PS4]
      ,[SO_NO4]
      ,[Part_code5]
      ,[PS5]
      ,[SO_NO5]
      ,[Engineer name]
  FROM ['NewLP']
  where (
        REASON_TEXT = 'Parts Not available (ASC)'
  or    REASON_TEXT = 'Parts In Transit (Samsung)'
  or    REASON_TEXT = 'Parts Back Ordered (Samsung)'
        )
  and   PS1 = 'U' 
  and   ( PS2 = 'U' or PS2 = '' )
  and   ( PS3 = 'U' or PS3 = '' )
  and   ( PS4 = 'U' or PS4 = '' )
  and   ( PS5 = 'U' or PS5 = '' )