erfan erfan - 3 months ago 13
SQL Question

unrelated data showing in the result(And,OR condition)?

this is my query:
SELECT TOP 10000 [REASON_TEXT]
,[PS1]
,[PS2]
,[PS3]
,[PS4]
,[PS5]
FROM Samsung.[dbo].['NewLP']
where (
REASON_TEXT = 'Not'
or REASON_TEXT = 'In'
or REASON_TEXT = 'Back'
)
and PS1 = 'U'
and PS2 = 'U' or PS2 = ''
and PS3 = 'U' or PS3 = ''
and PS4 = 'U' or PS4 = ''
and PS5 = 'U' or PS5 = ''


so the where clause should have those three values and PS1 should have the 'U' value and PS2 should have 'U' or it could be null and so as the rest of the PS's(PS3,PS4,PS5).

The result that I'm getting, the first two PS's are correct but starting the third 'and' ,which is the PS3 it's like the condition somehow Ignored?!?

here is the result:

enter image description here

I need to return those selected ones(red rectangle around them).
which part of the code is wrong.
I've tried every way but nothing returned the result that I need.
I tried this code for my condition as well but nothing showed.

and PS1 = 'U'
and PS2 <> 'P'
and PS3 <> 'P'
and PS4 <> 'P'
and PS5 <> 'P'


Appreciate any help.

Answer

Taking the REASON_TEXT values from your previous question and looking at your expected result this comes to mind:

SELECT TOP 10000 [REASON_TEXT]
      ,[PS1]
      ,[PS2]
      ,[PS3]
      ,[PS4]
      ,[PS5]    
FROM Samsung.[dbo].['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 IS NULL )
and   ( PS3 = 'U' or PS3 IS NULL )
and   ( PS4 = 'U' or PS4 IS NULL )
and   ( PS5 = 'U' or PS5 IS NULL )

Namely, you need to check for nulls instead of empty strings.

Comments