Failure 2620 The format or data contains a bad character

Database
Enthusiast

Failure 2620 The format or data contains a bad character

SELECT TRIM((CASE WHEN mt.Store_state IS NULL THEN '#@@#' ELSE mt.Store_state END)) as Store_State 

,TRIM((CASE WHEN mt.Company_Cd IS NULL THEN '#@@#' ELSE mt.Company_Cd END)) as Company_Cd
,TRIM((CASE WHEN mt.Sales_Org_Cd IS NULL THEN '#@@#' ELSE mt.Sales_Org_Cd END)) as Sales_Org_Cd
,TRIM((CASE WHEN mt.Fiscal_Period IS NULL THEN '#@@#' ELSE mt.Fiscal_Period END)) as Fiscal_Period
,TRIM((CASE WHEN mt.Fiscal_Year IS NULL THEN '#@@#' ELSE mt.Fiscal_Year END)) as Fiscal_Year
,(CAST(TRIM((CASE WHEN mt.Sales_Amt IS NULL THEN '#@@#' ELSE mt.Sales_Amt END))AS INTEGER) ) as Sales_Amt
CAST(TRIM((CASE WHEN mt.Tax_Amt IS NULL THEN '#@@#' ELSE mt.Tax_Amt END))AS INTEGER) ) as Tax_Amt
FROM my_table mt
WHERE
mt.Fiscal_Year=$fiscal_year
and mt.Fiscal_Period=$fiscal_period
and mt.Company_Cd=$company_code
group by 1,2,3,4,5,6,7,8,9,10,11,12,13

7 REPLIES
Enthusiast

Re: Failure 2620 The format or data contains a bad character

Can anybody help me with this error pls ...

Supporter

Re: Failure 2620 The format or data contains a bad character

Your need to share the DDL of my_table.

It looks like you di some internal data type conversions. It is likely that you fail at sales_amt and / or Tax_AMT.

In case these are NULL you map them to '#@@#' and then you want to cast them to an integer, which is not possible...

Junior Contributor

Re: Failure 2620 The format or data contains a bad character

When Sales_Amt or Tax_Amt is null you try to cast '#@@#' as an integer.

Btw, this is the same as your trim/case:

coalesce(trim(col), '#@@#')

Dieter

Enthusiast

Re: Failure 2620 The format or data contains a bad character

I guess there are three problems in your SQL

1. As specified by Ulrich & Dieter, it seems like Sales_Amt or Tax_Amt is NULL and caz you are returning character data '#@@#' in case of null values and casting it to INTEGER results in the error.

Just to be sure, add a clause in where to avoid null values for Sales_Amt and Tax_Amt. The query should run.

2. The other problem in the query is that you have an extra bracket in the last CAST statement. You need to remove one from the end caz it doesn't have an opening bracket.

3. The last CAST statement is not separated by a comma. You will need to add that as well.

Enthusiast

Re: Failure 2620 The format or data contains a bad character

I have the same problem

SEL FIELD_HDR_NM
FROM
V_FIELD_DATA_TYPE_CHK
WHERE
V_FIELD_DATA_TYPE_CHK.TYP_CHK_CD
NOT IN ('SUCCESS', 'DATE', 'TIMESTAMP');

gives 2620 error.

whereas

SEL FIELD_HDR_NM
FROM
V_FIELD_DATA_TYPE_CHK

works fine. Please can anyone help

Junior Contributor

Re: Failure 2620 The format or data contains a bad character

What's the datatype of TYP_CHK_CD?

Enthusiast

Re: Failure 2620 The format or data contains a bad character

Hi drmkd17,

I tried the query you had posted and it's working fine. 

As Dieter mentioned, could you please confirm the data type?

create volatile table V_FIELD_DATA_TYPE_CHK


FIELD_HDR_NM numeric,

TYP_CHK_CD varchar(30)

)

on commit preserve rows;

insert into V_FIELD_DATA_TYPE_CHK ('1','SUCCESS')

insert into V_FIELD_DATA_TYPE_CHK ('2','DATE')

insert into V_FIELD_DATA_TYPE_CHK ('3','TIMESTAMP')

insert into V_FIELD_DATA_TYPE_CHK ('4','ABCD')

sel * from V_FIELD_DATA_TYPE_CHK

FIELD_HDR_NM TYP_CHK_CD

1                       SUCCESS

2                       DATE

3                       TIMESTAMP

4                       ABCD

SEL FIELD_HDR_NM

FROM

V_FIELD_DATA_TYPE_CHK

WHERE

V_FIELD_DATA_TYPE_CHK.TYP_CHK_CD 

NOT IN ('SUCCESS', 'DATE', 'TIMESTAMP');

FIELD_HDR_NM

4