Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 4383 StartFragment: 314 EndFragment: 4351 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet
-- The SQL Code is failing with the following error -
create multiset Volatile table abba999_INPUT_MAY2017 as (sel CAST ( CUST_ID AS DECIMAL (18,0) ) AS CUST_ID, alloc_id, CAST( alloc_cnt AS INTEGER ) AS alloc_cnt, CAST ( RANKING AS INTEGER ) AS RANKING from abba999_INPUT_201705 ) with data primary index(cust_id, alloc_cnt) ON COMMIT PRESERVE ROWS;
*** The Table structure of abba999_INPUT_201705 is
Show table abba999_INPUT_201705;
CREATE MULTISET TABLE abba999_INPUT_201705,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
cust_id VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
alloc_cnt VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
alloc_id VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
RANKING VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( cust_id ,alloc_cnt );
How to convert the Var char to Decimal???
You got bad data, to find it you can use TO_NUMBER (which returns NULL instead of failing):
select CUST_ID from abba999_INPUT_201705 WHERE CUST_ID <> '' AND TO_NUMBER (CUST_ID) IS NULL
The below sql is failing with the following error -
SELECT Failed. 5628: Column AND TO_NUMBER (CUST_ID) IS NULL not found in UD124.YCA733_PY_INPUT_201705.
"Column AND TO_NUMBER (CUST_ID) IS NULL not found"
Can you show the actual SQL, looks like you're using something double quotes in the wrong place.