SQL error message (INTEGER FUNCTION)

[irinnew]

Hi experts,

I run the program in SAS environment against DATAMART IN DATA WAREHOUSE.

For some reason the fragment of code below failed. I have no idea why it generated the error message at the bottom.

What is invalid character in the character string argument of the function?

res_val field is a character type field. I am trying to convet it to numeric and to set 999 to all invalid values.

Could you please give me a hand?

Thank You in advance,

Irin



1391 EXECUTE(
1392 declare global temporary table qsrv
1393 ( Id char (29)
1394 , First_Srv_Dt date
1395 , rslt_cd char (13)
1396 , res_val char (18)
1397 , Num_Res_Val smallint
1398 )
1399 in "&sestblspc"
1400 on commit preserve rows not logged
1401 ) by HDMconnect;
1402
1403 EXECUTE(
1404 insert into session.qsrv
1405 select qu.Id
1406 , qu.dos
1407 , qu.rslt_cd
1408 , qu.res_val,
1409
1410 CASE
1411 WHEN qu.res_val='Invalid' OR
1412
1417 qu.lr_res_val= ' '
1418 THEN 999
1419 ELSE INTEGER(qu.res_val)
1420 END AS Num_Res_Val
1421 from &clmschema..v_l_data qu
1422 join &tmpschema..num np
1423 on qu.Id = np.Id
1424
1425 ) by mYconnect;
ERROR: CLI execute error: [IBM][CLI Driver][DB2/AIX64] SQL0420N Invalid character found in a
character string argument of the function "INTEGER". SQLSTATE=22018

 

[klaz2002]

irinnew,
This is a SQL problem not a SAS problem. You are trying to force the ANSI SQL driver to insert the digits 999 into a char field. You could use quotes and try "999" if that is what you want but I think that you are trying to do something else. Perhaps try to insert the value 999 into another interger var.
Klaz

 

[irinnew]

Klaz,


However I named it with a new name
(END AS Num_Res_Val) in order to insert into Num_Res_Val ( smallint) at the top of program....

Now I tried another function instead Integer...
1)When I use coalesce(cast(nullif(qu.res_val,'' ) as decimal(16,2)),0) I get:
****************************************************************************

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:
****************************************************************************

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

3)even tried to_number(res_val)-same error


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

In the 1st case however I am not sure if the function is accepted or not. Why it does complains to DECIMAL????

Confused...

 

[ChrisW75]

Do a proc freq on the column res_val first to confirm that you don't have any character values in it.