I failed to convert string to numeric (keeping decimal) and cleaning d

[irinnew]

I run the SAS program in datamart within datawarehouse (DB2 SQL)
I am trying to convert string values under qu.res_val field to numeric (keeping decimal
if any) and substituting all "dirty" data with 999

EXECUTE(
create table &tmpschema..gsrv
( Id char (29)
, First_Srv_Dt date
, rslt char (13)
, res_val char (18)
, Num_Res_Val smallint
)
in "&twotblspc"
not logged initially
) by MYconnect;
EXECUTE(
insert into &tmpschema..gsrv
select qu.Id
, qu.dos
, qu.rslt
, qu.res_val,
case
when qu.res_val= '>18' then 19
when qu.res_val='Invalid' OR
qu.res_val=' '
then 999
else coalesce(cast(nullif(qu.res_val,'') as decimal(16,2)),0)
end AS Num_Res_Val
from &clmschema..v_l_data qu
join &tmpschema..num np
on qu.Id = np.Id

) by Myconnect;

1)When I use coalesce(cast(nullif(qu.res_val,'' ) as decimal(16,2)),0) I get:
****************************************************************************

55 then 999
156 else coalesce(cast(nullif(qu.res_val,'') as decimal(16,2)),0)
157 end AS Num_Res_Val
158 from &clmschema..v_l_data qu
159 join &tmpschema..num np
160 on qu.Id = np.Id
161
162 ) 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
163

2)When I use ISNUMERIC ()I get:
****************************************************************************
25 then 999
526 else isnumeric(qu.res_val)
527 end AS Num_Res_Val
528 from &clmschema..v_l_data qu
529 join &tmpschema..num np
530 on qu.Id = np.Id
531
532 ) by Myconnect;

CLI execute error: [IBM][CLI Driver][DB2/AIX64] SQL0440N No authorized routine named
"ISNUMERIC" of type "FUNCTION" having compatible arguments was found.
SQLSTATE=42884


In the 2nd case it sounds like the function is not accepted by syntax

In the 1st case I am not sure if the function is accepted or not. If it is just a matter of some unrecognized invalid character then Why it does complains to DECIMAL????

Could you please help me as it is an urjent issue?

Thank you in advance!

Irin

 

[blom0344]

I am sure if I'm mistaken here, but are you trying to cast a non-numeric value as a decimal?
This could very well be an illegal operation, with resulting error generated.



Ties Blom
Information analyst