All Forums Analytics
gfuller40 24 posts Joined 05/10
17 Oct 2012
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

 

 

Qaisar Kiani 337 posts Joined 11/05
17 Oct 2012

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.

gfuller40 24 posts Joined 05/10
17 Oct 2012

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!

Qaisar Kiani 337 posts Joined 11/05
17 Oct 2012

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

gfuller40 24 posts Joined 05/10
18 Oct 2012

Are you saying I should not cast C_POL as decimal?

Qaisar Kiani 337 posts Joined 11/05
18 Oct 2012

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...

gfuller40 24 posts Joined 05/10
18 Oct 2012

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

Qaisar Kiani 337 posts Joined 11/05
18 Oct 2012

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

 

You must sign in to leave a comment.