Case statement with multiple else

General
Enthusiast

Case statement with multiple else

I have requirement where i have write case statements for the below

 case when substr(bill_cyc_cd ,1,1) = 'B' and acct_typ_cd = 'C' then 'Y' else 'N' end

 else case when substr(bill_cyc_cd ,1,1) = 'G' then 'Y' else 'N'  end bill_cycle

Above syntax doesnt work and it looks like it will work only in SQL server.

What is the best way to accomplish this in Teradata

19 REPLIES
Enthusiast

Re: Case statement with multiple else

case when substr(bill_cyc_cd ,1,1) = 'B' and acct_typ_cd = 'C' then 'Y' 
 when substr(bill_cyc_cd ,1,1) = 'G' then 'Y'
 else 'N'  end bill_cycle
Enthusiast

Re: Case statement with multiple else

thanks it worked

Enthusiast

Re: Case statement with multiple else

but I have another requirement

case when substr( pay_plan_ind ,1,1) = 'B' and acct_typ_cd = 'C' then 'D' else 'I' end

 else case when substr(bill_cyc_cd ,1,1) = 'G' then 'X' else 'U'  end bill_cycle

what is the best syntax to achieve the above in TD

Enthusiast

Re: Case statement with multiple else

Can someone help me with the above problem

Enthusiast

Re: Case statement with multiple else

Assigning two values for a single column will not be possible. There should be a condition(s) for either  I or U.

Teradata Employee

Re: Case statement with multiple else

Please have a look at following link, it explains the CASE statement:

http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1145_111A/ch02...

Highlighted
Enthusiast

Re: Case statement with multiple else

Hello,

 

I'm new here... as I try to search on Google and I found this forum interesting.  I have the SQL server script (please see below) as I want the first leter "P" in Column [LOCATION_CODE] match then it should show "Active" in column [LOC_TYPE] if not then it should show "Case Pick". However, some how it is not working as it doesn't matter it always show "Case Pick".  If some one can help me to make it work that would be great.  Thanks!

 

CASE WHEN SUBSTR(CD00_LOCATION, 1,1) = 'P' THEN 'Active'
ELSE 'Case Pick' END LOC_TYPE,

 

9-29-2017 2-00-35 PM.jpg

 

Apprentice

Re: Case statement with multiple else

Hi,

I can't see anything obviously wrong in your code.

Can you please post some input data - content of column CD00_LOCATION?

 

Also, check that you're not running in ANSI mode and that the data actually contains lower-case 'p', that would cause this.

 

(To check for ANSI mode, logon using SQL Assistant or Teradata Studio and run "HELP SESSION;" One of the returned columns will be 'transaction mode' (or maybe 'Transaction Semantics' - can't remember).

 

Have you got any spaces in front of the first letter in that column?

 

What release of TD are you using? "SELECT * FROM DBC.DBCInfov;"

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Case statement with multiple else

Hi DaveWellman,

 

Here is the original script I try to run from SQL server but the result is not that I want... I want the column [LOC_TYPE] display "Active" when only the first letter "P" as display on [LOCATION_CODE] ... if it is not letter "P" it have to display "Case Pick" ... However, it always keep display "Case Pick" even the fist letter is "P".  This try me crazy as I don't know how to figure out what I did wrong in the SQL script as it is not display "Active" when the fisrt letter is "P" in [LOC_TYPE] column.  Once again, thank you so much for all your help regarding this.  Thanks!

 

SELECT * FROM
(
SELECT
CD00_PKT_CTL_NBR ORDER_NBR,
CD00_CASE_NBR BOX_NBR,

CD00_SKU SKU,
CD00_LOCATION LOCATION_CODE,
CASE WHEN SUBSTR(CD00_LOCATION, 1,1) = 'P' THEN 'Active'
ELSE 'Case Pick' END LOC_TYPE,
CD00_DLM DLM
from vancouver.cdcart00, vancouver.phpick00
)
Where DLM between '27-SEP-2017' and '28-SEP-2017'

 

 

Display Result:

 

img_1.jpg