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.
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
Statement# 1, Info =0
*** Total elapsed time was 1 second.
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.
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.
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' )
ELSE CAST('1901-01-01' AS DATE FORMAT 'YYYY-MM-DD')
END AS Party_Locator_Start_Dt