Teradata Select Case Statement Help

Tools
Highlighted

Teradata Select Case Statement Help

I am primarily a SQL Server developer but working on an App with Teradata backend.

I am having problems developing some SQL code for Teradata ...

This is what I am needing ...

I have one table that holds the following:

APPL.FR_DETAIL

PO_NBR PO_LINE_NBR AD_NO_ADD ADMIN_FEE
------ ----------- --------- ---------
12345 1 A 0
12345 2 A 0
12345 3 A 0
12345 4 N 0
12345 5 N 0
12344 1 A 0
12344 2 A 0
12344 3 N 0
12344 4 N 0

OK, then i have a table that stores the admin_fee values ..

APPL.ADMIN_TBL

AD_FEE NO_AD_FEE
------ ---------
400 200

now, i need to update the above data to look like the following:

PO_NBR PO_LINE_NBR AD_NO_ADD ADMIN_FEE
------ ----------- --------- ---------
12345 1 A 400
12345 2 A 0
12345 3 A 0
12345 4 N 200
12345 5 N 0
12344 1 A 400
12344 2 A 0
12344 3 N 200
12344 4 N 0

The fee's are grouped at a PO_NBR level.

in SQL Server, I could do the statement like this:

UPDATE APPL.FR_DETAIL SET ADMIN_FEE =
CASE WHEN AD_NO_ADD = 'A' THEN
SELECT AD_FEE FROM APPL.ADMIN_TBL
ELSE
SELECT NO_AD_FEE FROM APPL.ADMIN_TBL
END

THEN, I'D do the deletion of the extra admin fees, so it will only have one fee for each PO / AdNoAd combo ...

but, the sub select in the case statmeent doesn't work in Teradata ... is there any other way i can do this?

Thanks,




1 REPLY

Re: Teradata Select Case Statement Help

BOTH THE QUERIES WILL WORK
******************************************************
UPDATE APPL.FR_DETAIL
FROM APPL.ADMIN_TBL
SET ADMIN_FEE = CASE WHEN APPL.FR_DETAIL.AD_NO_ADD = 'A'
THEN APPL.ADMIN_TBL.AD_FEE ELSE APPL.ADMIN_TBL.NO_AD_FEE END
******************************************************
UPDATE APPL.FR_DETAIL
FROM APPL.ADMIN_TBL
SET ADMIN_FEE = CASE APPL.FR_DETAIL.AD_NO_ADD WHEN 'A'
THEN APPL.ADMIN_TBL.AD_FEE ELSE APPL.ADMIN_TBL.NO_AD_FEE END
******************************************************