Problem with WHERE (AND) and (OR)

Transact-SQL
1

The following is a Where statement of a long query

WHERE XA.INIT_APP = 'GEIA-0007'

AND XB.LCNTYPXB = 'p'

AND ERNLOC.UOCSEIXC ='AZ1'

OR ERNLOC.UOCSEIXC ='AZ0'

OR ERNLOC.UOCSEIXC ='AY0'

OR ERNLOC.UOCSEIXC ='AU0';

The first 2 rows work fine, the problem is the next 4 rows, the query provides data for only 3 rows AZ1, AZ0 and AY0 and gives nothing for AU0. I tried to change the order but the problem persists. My question is there any limitation with AND and OR where you have to use parentheses any help would be very appreciated.

2

hi

hope this helps

WHERE XA.INIT_APP = 'GEIA-0007'
AND XB.LCNTYPXB = 'p'
AND 
(
	ERNLOC.UOCSEIXC ='AZ1'
	OR ERNLOC.UOCSEIXC ='AZ0'
	OR ERNLOC.UOCSEIXC ='AY0'
	OR ERNLOC.UOCSEIXC ='AU0'
);
3

another way it can be re written

WHERE XA.INIT_APP = 'GEIA-0007'
AND XB.LCNTYPXB = 'p'
AND 
ERNLOC.UOCSEIXC IN ('AZ1','AZ0','AY0','AU0')
4

I try both and still not working, Just to let you I use this query in an APEX query builder (Oracle)

5

I am using Microsoft SQL Server

Please see the documentation for your platform
Its all Static .. should be easy to read and figure out

of course testing ..by creating ..sample data .. is also there

6

What is funny no matter witch one I used it is always the first 'AZ1' that is missing. I change the first one with 'AZ0' and now is 'AZ0' that are missing.

Event try this

WHERE (((XB.LCNTYPXB) = 'p')
AND ((ERNLOC.UOCSEIXC) ='AZ1'))
OR (((ERNLOC.UOCSEIXC) ='AZ0'))
OR (((ERNLOC.UOCSEIXC) ='AY0'))
OR (((ERNLOC.UOCSEIXC) ='AU0'));

Same problem all AZi record are not showing

7

should i show you what's happening in Microsoft SQL Server T-SQL ??

8

No it's OK

I run it on the SQL Plus prompt and still have the same problem, very bizare

9

these sort of things are VERY VERY common in software

question is
How are you going to figure out what's happening

10

I try several way of writing on the SQL Plus prompt and found the solution, when it's written like this is work

WHERE ERNLOC.UOCSEIXC ='AZ1' OR ERNLOC.UOCSEIXC = 'AZ0' OR ERNLOC.UOCSEIXC = 'AY0' OR ERNLOC.UOCSEIXC = 'AU0' AND XB.LCNTYPXB = 'P';

Thank you very much for your time.

11

Hi

Trial and error
Trying all combinations

Is one way to find out

There are lots of other ways
Anyhow ..
All the best
:+1::+1: