Print
All Forums Database
anjunair 13 posts Joined 04/12
03 Aug 2012
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
Tags:
anjunair 13 posts Joined 04/12
03 Aug 2012

Can anybody help me with this error pls ...

ulrich 597 posts Joined 09/09
03 Aug 2012

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

 

Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles

dnoeth 1928 posts Joined 11/04
03 Aug 2012

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

Qaisar A...ab Kiani 232 posts Joined 11/05
03 Aug 2012

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.

You must sign in to leave a comment.