Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    13

    Unanswered: How to substitute Substr() function?

    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,

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    substr is a valid function. What is the data type of ms.SPEC_CD?

    Andy

  3. #3
    Join Date
    Jan 2005
    Posts
    13
    character

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •