Nested Update - Case issue

Data Modeling
Enthusiast

Nested Update - Case issue

Hi all - 

I am looking for some input on an UPDATE statement. I am still new to Teradata and looking to do an update based on certain conditions.. Below is the statement i have right now..Please let me know if i have left anything out... I'm not sure if it would be better to split this up or use this type of case statement..

UPDATE database.Table 
SET ActualPrcAfterDiscount =

CASE
WHEN MultipleCharges = 'Y' AND Amount= 0
THEN 0

CASE
WHEN MultipleCharges<> 'Y' AND COALESCE(PrcOveride, PrcAfterDiscount)
ELSE

CASE
WHEN MultiCharge = 'Y'
THEN Amount
* (CASE
WHEN SpecialCmpnCd IS NOT NULL AND COALESCE(PrcOveride, PrcAfterDiscount)/Amount= 1.0
THEN 0
ELSE COALESCE (PrcOveride, PrcAfterDiscount)
END
)
ELSE
(CASE
WHEN SpecialCmpnCd IS NOT NULL AND COALESCE(PrcOveride, PrcAfterDiscount)/Amount= 1.0
THEN 0
ELSE COALESCE (PrcOveride, PrcAfterDiscount)
END
)
END
END

The error that i am recieving is - 

UPDATE Failed.  [3707] Syntax error, expected something like a 'SUCCEEDS' keyword or a 'MEETS' keyword or a 'PRECEDES' keyword or an 'IN' keyword or a 'CONTAINS' keyword between ')' and the 'THEN' keyword.

1 REPLY
Junior Contributor

Re: Nested Update - Case issue

There's a lot of invalid syntax, too many CASEs and no comparison for the first COALESCE.

Try to get your syntax running with a SELECT first.