All Forums Database
MBR 29 posts Joined 01/13
11 Mar 2013
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

Bala
KS42982 137 posts Joined 12/12
11 Mar 2013

Try character_length function instead of length.

Adeel Chaudhry 773 posts Joined 04/08
11 Mar 2013

Yes, CHARACTER_LENGTH will surely work.

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

MBR 29 posts Joined 01/13
11 Mar 2013

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.
 

Bala

barani_sachin 141 posts Joined 01/12
11 Mar 2013

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.

ocirej23 10 posts Joined 04/11
11 Mar 2013

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 29 posts Joined 01/13
11 Mar 2013

Hi Barani,
its working fine after casting it to varchar() . thanks for valuable suggestion.
Regards

Bala

MBR 29 posts Joined 01/13
11 Mar 2013

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

Bala

You must sign in to leave a comment.