Converting string into a numeric (keep decimal) and substitute dirty d

[irinnew]

Hi Experts,
I run the program against datamart in datawarehouse (DB2 SQL)
I am trying to convert string VALUE UNDER qu.res_val FIELD to numeric (keeping decimal
if any) and substituting all "dirty" data with 999
System generates the Error at the very bottom.
What I am doing wrong?

Thank you in advance!
Irin
*****************************************
EXECUTE(
1401 declare global temporary table qsrv
1402 ( Id char (29)
1403 , Dt date
1404 , rslt char (13)
1405 , res_val char (18)
1406 , Num_Res_Val smallint
1407 )
1408 in "&sestblspc"
1409 on commit preserve rows not logged
1410 ) by Myconnect;
1411
1412 EXECUTE(
1413 insert into session.qsrv
1414 select qu.Id
1415 , qu.dos
1416 , qu.rslt
1417 , qu.res_val,
1418
1419 CASE
1420 WHEN qu.res_val='Invalid' OR
1421
1426 qu.res_val= ' '
1427 THEN 999
1428 ELSE coalesce(cast(nullif(qu.res_val,'') as decimal(16,2)),0)

1429 END AS Num_Res_Val
1430 from &clmschema..v_l_data qu
1431 join &tmpschema..num np
1432 on qu.Id = np.Id
1433
1434 ) by MyConnect;
ERROR: CLI execute error: [IBM][CLI Driver][DB2/AIX64] SQL0420N Invalid character

found in a
character string argument of the function "DECIMAL". SQLSTATE=22018
1435

 

[LKBrwnDBA]

Change this:

nullif(qu.res_val,'')

To this:

nullif(qu.res_val,'0')



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[rac2]

Possibly the data is dirtier than you know, that is, possibly there are non-numeric characters in the res_val field. DB2 probably has a function to check whether a string could be a number. In T-SQL the function is ISNUMERIC( string_expresssion ). That might be a better choice in the CASE expression.

That the error message refers to a function "DECIMAL" seems odd. Is there something you havent told us?

You may get better answers in the DB2 forum. The ANSI SQL forum is more about the SQL language standard than particular RDMSs.

 

[LKBrwnDBA]

Actually DO NOT USE NUNLLIF() function, use VALUE() function:

VALUE(qu.res_val,0)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb