All Forums Data Modeling
danimaltex 8 posts Joined 10/14
22 Oct 2014
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.

Thanks, Dan
dnoeth 4628 posts Joined 11/04
22 Oct 2014

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.

Dieter

You must sign in to leave a comment.