LENGTH() function in bteq

Database
MBR
Enthusiast

LENGTH() function in bteq

Hi,

can anyone tell me what is the equalent function to use in BTEQ instead of LENGTH(col)

i am getting error while running the script through bteq, i have used LENGTH() function inside the script so its giving error.

Regards

7 REPLIES
Enthusiast

Re: LENGTH() function in bteq

Try character_length function instead of length.

Teradata Employee

Re: LENGTH() function in bteq

Yes, CHARACTER_LENGTH will surely work.

MBR
Enthusiast

Re: LENGTH() function in bteq

Thanks for reply.

But CHARACTER_LENGTH is also not working. I have used CHAR_LENGTH() as well but it is also giving error.

the below error i am getting while running the script through BTEQ.

 *** Failure 3580 Illegal use of CHARACTERS, MCHARACTERS, or OCTET_LENGTH fu

 nctions.

                Statement# 1, Info =0

 *** Total elapsed time was 1 second.

Enthusiast

Re: LENGTH() function in bteq

Hi MBR,

     If you are trying to use the above said functions on numeric columns it will throw the error u mentioned; However if you want to find the length of the numeric columns, cast it  to varchar and then use any of the length functions.

Enthusiast

Re: LENGTH() function in bteq

Hi,



Can you provide the SQL you use in BTEQ? I believe it has something to do with data type of the string itself. I think it has to be a varchar data type. Please do cast the value first to varchar then apply the function CHAR_LENGTH.

HTH.

Regards,

Jerico

MBR
Enthusiast

Re: LENGTH() function in bteq

Hi Barani,

its working fine after casting it to varchar() . thanks for valuable suggestion.

Regards

MBR
Enthusiast

Re: LENGTH() function in bteq

Hi,

In below query i am using LENGTH(),I have used CHARCTER_LENGTH() in place of LENGTH

any way its working fine after casting it to varchar()...Thanks folks.

CASE WHEN cast(CHARACTER_LENGTH(TCLIA.ADDR_STAT_CHNG_DT ) as varchar(10)) =10 THEN

CASE WHEN TCLIA.ADDR_STAT_CHNG_DT is null THEN

CAST('1901-01-01' AS DATE FORMAT 'YYYY-MM-DD')

ELSE CAST( TCLIA.ADDR_STAT_CHNG_DT AS DATE FORMAT 'YYYY-MM-DD' )

END

ELSE CAST('1901-01-01' AS DATE FORMAT 'YYYY-MM-DD')

END AS Party_Locator_Start_Dt

Regards

MBR