I run the following query:

EXECUTE(
declare global temporary table Hospice_prov
( Id char (29)
,Srv_Dt date
,End_Dt date

)

) by Mconnect;

EXECUTE(


EXECUTE(
insert into session.prov
select distinct
ms.Id
, mc.Start_Dt as Srv_Dt
, mc.End_Dt

from &clmschema..MedS ms
join &clmschema..MedC mc
on ms.Id = mc.Id
where SUBSTR(ms.SPEC_CD,1,2)='32'
and mc.Start_Dt >= '01/01/2005'
and mc.End_Dt <= '12/31/2007'

) by Mconnect;

************************************************** *******

Error log is:

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

************************************************** ****

It looks like Substr function is not appropriate to DB2 SYNTAX

WHAT SHOULD i REPLACE IT WITH?

Thank you in advance,

substr is a valid function. What is the data type of ms.SPEC_CD?

Andy

character

I do not know what the problem is, but this should get you what you want:

insert into session.prov
select distinct
ms.Id
, mc.Start_Dt as Srv_Dt
, mc.End_Dt

from &clmschema..MedS ms
join &clmschema..MedC mc
on ms.Id = mc.Id
where ms.SPEC_CD like '32%'
and mc.Start_Dt >= '01/01/2005'
and mc.End_Dt <= '12/31/2007'

Andy