CREATE TABLE Failed. 2621: Bad character in format or data of abba999_NPUT_201705.cust_id

Data Modeling
Enthusiast

CREATE TABLE Failed. 2621: Bad character in format or data of abba999_NPUT_201705.cust_id

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,

DEFAULT MERGEBLOCKRATIO

(

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

 

4 REPLIES
Enthusiast

Re: CREATE TABLE Failed. 2621: Bad character in format or data of abba999_NPUT_201705.cust_id

We are in TD 15 version

Junior Contributor

Re: CREATE TABLE Failed. 2621: Bad character in format or data of abba999_NPUT_201705.cust_id

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
Enthusiast

Re: CREATE TABLE Failed. 2621: Bad character in format or data of abba999_NPUT_201705.cust_id

Hi Dnoeth,

 

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. 

Junior Contributor

Re: CREATE TABLE Failed. 2621: Bad character in format or data of abba999_NPUT_201705.cust_id

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