Case statement returning "Bad character in format or data...."

Analytics

Case statement returning "Bad character in format or data...."

I'm trying to run the following code to return a policy# formatted correctly so that I can join up with other tables and I'm receiving the following error "Bad character in format or data of TLTC900_CLM.C_POL

Any help would be great!

SELECT I_SYS_CLM
,D_EFF_RCD
,C_CLM
,C_STA_CLM
,C_TYP_SYS_IDV
,I_SYS_IDV
,C_POL
,C_AMS_CLM
, I_PDT_GL, C_PLN_QLF
,case when i_pdt_gl in('12984','22984') then trim(cast(cast(clm.C_POL as decimal (10,0) format '9999999999') as char(10)))
when i_pdt_gl not in('362114','362154') and i_pdt_gl not in('364154','364114')/*adept*/ then clm.c_pol
when i_pdt_gl in('362114','362154') then/* merlin*/
cast(cast(case when substr(clm.C_POL,1,1) = 'L' then '00000000'
when udf_isnumeric(clm.C_POL) = 1 then substring(clm.C_POL from 1 for char_length(trim(clm.C_POL))-2)
else clm.C_POL
end as decimal (8,0) format '99999999') as char(8))
when i_pdt_gl in('364154','364114')/*adept*/ and udf_isnumeric(clm.c_pol) = 0 then
cast(cast(clm.C_POL as decimal (8,0) format '99999999') as char(8))
else clm.c_pol
end as "Product_Name"

FROM pearl_p.TLTC900_CLM clm


7 REPLIES

Re: Case statement returning "Bad character in format or data...."

Seems like the column C_POL contains some characters data and in the case statement where you are casting the column to DECIMAL is causing the error because of the character data.

Why don't you use the udf_isnumeric() to check whether the column contains the numerics or not before casting it in decimal.

Re: Case statement returning "Bad character in format or data...."

you are right - I broke this case statement out case by case and the first line was the one sending me the message. I'm not really sure the syntax for this? is there an example anywhere?

Thanks for taking the time to help with this!

Re: Case statement returning "Bad character in format or data...."

Ok, I am confused. Which syntax or example you are looking for?

Re: Case statement returning "Bad character in format or data...."

Are you saying I should not cast C_POL as decimal?

Re: Case statement returning "Bad character in format or data...."

You will always get an error if you you are casting a character data into numeric INTEGER/DECIMAL. You should check whether the content is numeric is or not before casting it.

I guess your UDF udf_isnumeric() is the one that can handle this scenario

when i_pdt_gl in('12984','22984') then  
CASE WHEN udf_isnumeric(clm.C_POL) = 1 THEN trim(cast(cast(clm.C_POL as decimal (10,0) format '9999999999') as char(10)))
ELSE 'BAD DATA'
END

I am guessing that the UDF udf_isnumeric that youa re using in your CASE statement is for the same purpose...

Re: Case statement returning "Bad character in format or data...."

Okay when i run that I'm still getting the same error rather than running and displaying "bad data"

Re: Case statement returning "Bad character in format or data...."

I think I put the condition wrong. When udf_isnumeric() returns 1, does it mean the data is numeric or non-numeric. The code is assuming that return code ZERO represents the NUMERIC content.

In this condition 'WHEN  I_PDT_GL IN('362114','362154') THEN.... ', you are checking whether the data is string or not. In case it is then you are trimming last two characters from it. Does this ensure that the remaining content is NUMERIC. Becasue you are casting it directly into DECIMAL after trimming last two characters, so it has to be numeric otherwise you get the same error.

Try the below code and see if it works out.

SELECT  I_SYS_CLM
,D_EFF_RCD
,C_CLM
,C_STA_CLM
,C_TYP_SYS_IDV
,I_SYS_IDV
,C_POL
,C_AMS_CLM
, I_PDT_GL, C_PLN_QLF
,CASE WHEN I_PDT_GL IN('12984','22984') THEN
CASE WHEN UDF_ISNUMERIC(CLM.C_POL) = 0 THEN TRIM(CAST(CAST(CLM.C_POL AS DECIMAL (10,0) FORMAT '9999999999') AS CHAR(10)))
ELSE 'BAD DATA'
END
WHEN I_PDT_GL NOT IN('362114','362154') AND I_PDT_GL NOT IN('364154','364114')/*ADEPT*/ THEN
CLM.C_POL
WHEN I_PDT_GL IN('362114','362154') THEN/* MERLIN*/
CAST(CAST(CASE WHEN SUBSTR(CLM.C_POL,1,1) = 'L' THEN
'00000000'
WHEN UDF_ISNUMERIC(CLM.C_POL) = 1 THEN
SUBSTRING(CLM.C_POL FROM 1 FOR CHAR_LENGTH(TRIM(CLM.C_POL))-2)
ELSE CLM.C_POL
END AS DECIMAL (8,0) FORMAT '99999999') AS CHAR(8))
WHEN I_PDT_GL IN('364154','364114')/*ADEPT*/ AND UDF_ISNUMERIC(CLM.C_POL) = 0 THEN
CAST(CAST(CLM.C_POL AS DECIMAL (8,0) FORMAT '99999999') AS CHAR(8))

ELSE CLM.C_POL
END AS "PRODUCT_NAME"

FROM PEARL_P.TLTC900_CLM CLM