CASE function

Database

CASE function

Hi,

I have address stored in one of the 2 different fields, want to take data from the one which is poppulated...

Here is the code that I've used:

(case when CHARACTER_LENGTH(a.ADDR_1) > 5 then a.ADDR_1 else a.ADDR2 end) as ADDRESS

seems like Teradata SQL is not recognizing actual length of the address (using function CHARACTER_LENGTH) and is taking length of the field/field which is of type char 24...

I've al so tried following code (with no success though)

(case when a.ADDR_1 is null then a.ADDR2 else a.ADDR_1 end) as ADDRESS

how to fix it

Tags (1)
3 REPLIES
Enthusiast

Re: CASE function

Hi,
If the column defined as the CHAR.Then you have to use TRIM and then use char_lenght or CHAR function to get the length.Hope this works.

regards,
Rupesh

Re: CASE function

Hi Rupesh, can you please give me the exact functions... will this work...?

(case when char_length(trim(a.ADDR_1)) > 5 then a.ADDR_1 else a.ADDR2 end) as ADDRESS
Teradata Employee

Re: CASE function

Hi,

In the case of CHAR it will do exactly the same .... and yes it will work just fine with TRIM .... example as follows:

CREATE VOLATILE TABLE Tab1 (Col1 CHAR(24)) ON COMMIT PRESERVE ROWS;

INSERT Tab1 VALUES('123456');
INSERT Tab1 VALUES('1234');

SELECT Col1, (CASE WHEN CHARACTER_LENGTH(TRIM(Col1)) > 5 THEN 'GREATER THAN 5' ELSE 'LESS THAN OR EQUAL TO 5' END) AS ADDRESS FROM Tab1;