CODEWITHSUNDEEP
sqlt-sql
moe
moe

Reputation: 5249

how to use OR/AND operator together in sql ?

I am having issues getting this query to work, i am actually trying to use the OR and AND operator together but can't seem to figure out the problem as to why i am getting this error": "expectingABSOLUTE' or ACTION' orADD' or ALL' orALTER'"`. Here is my sql code:

SELECT * 
FROM EDXW ED, TRANSACTION TXN
WHERE ED.LAM_BK like 'TAR%'
AND ED.KEY = TXN.KEY
AND substring(ED.JAM_BK from position('~' in ED.JAM_BK) + 1) = TXN.EBS_ID

(AND ED.CLS_CD IN ('WIS','OP','OPP')
AND TXN.REV_CD IN ('0360','0361')
AND TXN.LS_CTR_CD IN ('548000','552000','552500','553000','553500','554000','555000','555500','558000')
AND ED.SITE_CD = 'X' )

OR

( ED.ED_CLS_CD IN ('WIS','OP','OPP')
AND AND TXN.REV_CD IN ('0350','0311')
AND AND TXN.LS_CTR_CD IN ('548000','558300')
AND LOC.ED_SITE_CD = 'M')

OR

( ED.ED_CLS_CD IN ('WIS','OP','OPP')
AND AND TXN.REV_CD IN ('0350','0311')
AND AND TXN.LS_CTR_CD IN ('549000','557300')
AND LOC.ED_SITE_CD IN ('M','K'));

Upvotes: 0

Views: 6427

Answers (2)

hol
hol

Reputation: 8423

I think the issue is that you say: a and b and c and d or e or f but I think you want to say a and b and c and (d or e or f) which is something very different. The whole expression becomes true when f is true or e is true. The braces make it clear. I think your select has to look like this: 

SELECT * 
FROM EDXW ED, TRANSACTION TXN
WHERE ED.LAM_BK like 'TAR%'
AND ED.KEY = TXN.KEY
AND substring(ED.JAM_BK from position('~' in ED.JAM_BK) + 1) = TXN.EBS_ID
AND
(
 (ED.CLS_CD IN ('WIS','OP','OPP')
 AND TXN.REV_CD IN ('0360','0361')
 AND TXN.LS_CTR_CD IN ('548000','552000','552500','553000','553500','554000','555000','555500','558000') 
 AND ED.SITE_CD = 'X' )

OR

 ( ED.ED_CLS_CD IN ('WIS','OP','OPP')
 AND TXN.REV_CD IN ('0350','0311')
 AND TXN.LS_CTR_CD IN ('548000','558300')
 AND LOC.ED_SITE_CD = 'M')

OR

 ( ED.ED_CLS_CD IN ('WIS','OP','OPP')
 AND TXN.REV_CD IN ('0350','0311')
 AND TXN.LS_CTR_CD IN ('549000','557300')
 AND LOC.ED_SITE_CD IN ('M','K'))
);

Upvotes: 4

Jeanne Boyarsky
Jeanne Boyarsky

Reputation: 12266

You've got extra parens or missing and/ors. Here's a simplified version that shows the problem:

SELECT * 
FROM tables
WHERE clause1
(AND clause2)

From SQL's point of view, this is incorrect. Possible correct versions are

SELECT * 
FROM tables
WHERE clause1
AND (clause2)

SELECT * 
FROM tables
WHERE clause1
OR (clause2)

See the difference? The parens group statements. So you can have more complex expressions like: SELECT * FROM tables WHERE clause1 AND (clause2 or clause3)

They key is that if you were to reduce everything in the parens to a single true/false value, the query would still make sense.

Upvotes: 3

Related Questions