Power

 View Only

  • 1.  SQL Stored Procedure

    Posted Tue September 06, 2022 09:38 AM
      |   view attached

     I need your help with a query in a stored procedure. 

     

    The Stored Procedure is being called by a user from the IBM i in a CLLE program. It reads a table (ACTIVITY23) and Insert the selected data (Based on the parameters) in the TR_DEBITS_CREDITS_REPORT

     

    The User will select one or multiple Bussiness Units (2 characters), it could up to 20 BUs.

     

    So I pass the 40 characters and date from and date to, to the Stored Procedure.

     

    In my Stored Procedure, I SET the Input variable in a Global Variable 

     

    My query will do an Insert in a table extracting the data from another table (ACTIVITY23).

     

    In the WHERE clause, I have tried different ways to search the Twenty Times Two characters. I have used the Like, I have used the IN with the entire field,,,

     

    Right now , I am using the IN CLAUSE searching in the Global Variable substring by two characters.

     

    This is my query:

     

     

    DELETE  LLAGPL . TR_DEBITS_CREDITS_REPORT;

     

    INSERT INTO LLAGPL . TR_DEBITS_CREDITS_REPORT ( 

    EMPLOYEE_NAME,EMP_ID    ,ActDbId_Description_To_Tracker,PROJECT#  ,TRACK00001,HOURS     ,

    TIME_IN   ,TIME_OUT  ,STAT_CD   ,ACT_CODE  ,ACTIVITY_DATE, PAY_PERIOD_END_DATE ,ACTID , AN8, MCU         

    )

    SELECT TRIM(ACTEMPNAME)  as Employee_Name  ,TRIM(ACTEMPID)  as  Emp_Id 

    ,   CASE    substr(ActDbId,30,10)

                    WHEN    'Credit]    ' THEN    'Transaction for ' concat substr(ActDbId,14,15)  concat  ' 2 '  concat  substr(ActDbId,30,10) --as ActDbId_Description_To_Tracker

                    WHEN    'Debit]     ' THEN    'Transaction for ' concat substr(ActDbId,14,15)   concat  ' 3 '  concat  substr(ActDbId,30,10) --as ActDbId_Description_To_Tracker

                    ELSE                                  'Transaction for ' concat  substr(ActDbId,14,15)   concat  ' 4 '  concat  substr(ActDbId,30,10) --as ActDbId_Description_To_Tracker

        END                

    , trim(ACTPRJCODE)  as  Project#  

    , trim(ACTDESCRIPTION)  as  Tracker_Description

    , ACTTIMEVALUE  as Hours, trim(ACTTIMEIN)  as  Time_IN  ,trim(ACTTIMEOUT)  as  Time_Out   

    ,trim(ACTDETAILCUSTOM1)  as  Stat_Cd  , trim(ACTIVITYCODE)  as  Act_Code   

    , SUBSTR(CHAR(ACTIVITYDATE),1,10)    as  Activity_Date   ,SUBSTR(CHAR(ACTWEEKENDDATE),1,10)    as  Pay_Period_End_Date  ,TRIM(ACTID) as ACTID, TO_NUMBER(substr(ACTEMPID,1,4)) , substr(ACTEMPCCCODE,1,2) as MCU         

    FROM   LLAGPL.ACTIVITY23

    --JOIN    OLADTA94.F060116

    --ON      TO_NUMBER(substr(ACTEMPID,1,4)) =   YAAN8

    WHERE   substr(actdbid,1,28) LIKE '%Debit/Credit%'

    AND       SUBSTR(CHAR(ACTIVITYDATE),1,10)    >= LLAGPL.GblDATE_3

    AND       SUBSTR(CHAR(ACTIVITYDATE),1,10)    <= LLAGPL.GblDATE_4

    AND       substr(ACTEMPCCCODE,1,2)                  IN  (   substr(LLAGPL.GblMCUSEL,1,2), substr(LLAGPL.GblMCUSEL,3,2), substr(LLAGPL.GblMCUSEL,5,2),                                                                                         substr(LLAGPL.GblMCUSEL,7,2), substr(LLAGPL.GblMCUSEL,9,2) , substr(LLAGPL.GblMCUSEL,11,2),

                                                                                     substr(LLAGPL.GblMCUSEL,13,2), substr(LLAGPL.GblMCUSEL,15,2), substr(LLAGPL.GblMCUSEL,17,2), substr(LLAGPL.GblMCUSEL,19,2),                                                                                 substr(LLAGPL.GblMCUSEL,21,2), substr(LLAGPL.GblMCUSEL,23,2),

                                                                                    substr(LLAGPL.GblMCUSEL,25,2), substr(LLAGPL.GblMCUSEL,27,2), substr(LLAGPL.GblMCUSEL,29,2)         )

    order by actDBID; 

     

    The only piece that is not working is the selection.

     

    I have tried the "  AND substr(ACTEMPCCCODE,1,2)                  IN         LLAGPL.GblMCUSEL, and the selection does not work.

    I have tried the "  AND substr(ACTEMPCCCODE,1,2)                  LIKE     LLAGPL.GblMCUSEL, and and the selection does not work.

     

    I thought about using arrays, but I can not find any documentation or sample.

     

    Thank you so much

     

    Carlos



    ------------------------------
    Carlos Irigoyen
    Senior Applications Engineer
    Louisiana Legislator Auditor
    Baton Rouge LA
    9542619893
    ------------------------------

    Attachment(s)

    docx
    SQL Stored Procedure Question.docx   19 KB 1 version