Reputation: 7520
I am writing a query to get all the invoices with amount > 100 for US > 200 for CAN and > 1200 for mxn ( mexico)
Invoice_Header table ( total amount and company id) Company table (company id and currency id) currency table ( currency id and currency)
SELECT IH.Invoice_Num , IH.invoice_amount , IH.invoice_date
FROM Invoice_Header IH ,
company c,
Currency cu
WHERE IH.Company_oid = c.Company_oid
AND c.currency_oid = cu.currency_oid
AND
CASE IH.total =
WHEN cu.code = 'USA' then IH.total > 100
WHEN cu.code = 'CAN' then IH.total > 200
WHEN cu.code = 'MXN' then IH.total > 1200
END
I am getting syntax errors when trying to run this on a oracle database.
Upvotes: 1
Views: 68
Reputation: 8544
I would try something like
SELECT IH.Invoice_Num , IH.invoice_amount , IH.invoice_date
FROM Invoice_Header IH ,
company c,
Currency cu
WHERE IH.Company_oid = c.Company_oid
AND c.currency_oid = cu.currency_oid
AND ( (cu.code = 'USA' and IH.total > 100)
OR (cu.code = 'CAN') and IH.total > 100)
OR (cu.code = 'MXN') and IH.total > 1200))
if you really need to use a CASE in the where clause you could do something like
SELECT 'WEEK-END' AS col_a
FROM DUAL
WHERE 1 = CASE
WHEN to_char(sysdate, 'Day') = 'Saturday' then 1
WHEN to_char(sysdate, 'Day') = 'Sunday' then 1
else 0
END
Upvotes: 1
Reputation: 231881
I wouldn't use a CASE
statement here. It sounds like you want some basic boolean logic
AND( (cu.code = 'USA' and ih.total > 100)
OR (cu.code = 'CAN' and ih.total > 200)
OR (cu.code = 'MXN' and ih.total > 1200))
If you really wanted to use a CASE
statement for some reason, you could do something like
AND 1 = (CASE WHEN cu.code = 'USA' and ih.total > 100
THEN 1
WHEN cu.code = 'CAN' and ih.total > 200
THEN 1
WHEN cu.code = 'MXN' and ih.total > 1200
THEN 1
ELSE 0
END)
Upvotes: 6