All Forums Database
tparvaiz 3 posts Joined 06/10
16 Jun 2010
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

RB185048 33 posts Joined 09/08
16 Jun 2010

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

tparvaiz 3 posts Joined 06/10
16 Jun 2010

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

Adeel Chaudhry 773 posts Joined 04/08
16 Jun 2010

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;

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.