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
group by 1,2,3,4,5,6,7,8,9,10,11,12,13
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...
When Sales_Amt or Tax_Amt is null you try to cast '#@@#' as an integer.
Btw, this is the same as your trim/case:
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.
I have the same problem
NOT IN ('SUCCESS', 'DATE', 'TIMESTAMP');
gives 2620 error.
works fine. Please can anyone help
I tried the query you had posted and it's working fine.
As Dieter mentioned, could you please confirm the data type?