Rocket DevOps (formerly Aldon)

 View Only

  • 1.  SQL Stored Procedure Substitution Variables

    Posted 10-28-2022 11:01
    In another tool, I was able to use a substitution variable to replace specific elements in an SQL statement.  For now I need to hard code the data library as the SP will be called without a library list.  Is this available and where do I find the list?  In this case, I need library group 2 from the setup definitions.  Then I would have PDQMST001 for ITG and ABCMST001 for QUA and PDN.  

        
    SELECT VSTATE AS State, ZIP, CITY, ad.VENDR, SEQN,VENDORNAME                                                
  FROM LIBRARY1.FILE2PF AD JOIN LIBRARY1.FILE1PF NM ON (ad.VENDR = Vendorno)                            
 WHERE (wk_State = ' ' or wk_State = vstate)                     
   AND (wk_City = ' '  or locate(wk_City,upper(trim(CITY))) <> 0)              
  AND (wk_Name = ' ' or locate(wk_Name,upper(trim(VName))) <> 0)        
 ORDER BY state, zip;                                            ​
    FROM LIBRARY1.FILE2PF AD JOIN LIBRARY.FILE1PF NM ON (ad.VENDR = Vendorno) WHERE (wk_State = ' ' or wk_State = vstate) AND (wk_City = ' ' or locate(wk_City,upper(trim(CITY))) <> 0) AND (wk_Name = ' ' or locate(wk_Name,upper(trim(VName))) <> 0) ORDER BY state, zip;

    ------------------------------
    David Taylor
    Senior Developer
    Range Resources Corporation
    Fort Worth TX US
    ------------------------------


  • 2.  RE: SQL Stored Procedure Substitution Variables

    ROCKETEER
    Posted 11-18-2022 09:48
    Posting answer that was provided in support case:
    First, to address your question about substitution variables. The only variable that is supported in a source member for non source-based objects (called pseudo source in LMi) is &LIB which is the target library for the object creation.

    With this in mind, we can suggest two possible options.
    Option 1: you could remove the library qualification from the file names and consider using the SET CURRENT SCHEMA statement. You will need to review the details of using this statement in the relevant IBMi documentation to determine if it would be applicable for your environment. 

    Option 2: Use the &LIB library qualification for the files referenced in the SQL procedure. Thats all that would be needed if the procedure is assigned to the same library group as the files. Otherwise create aliases pointing to the actual files and assigned them to the same library group as the procedure and reference those aliases with the &LIB qualification. This means that the FROM statement would look like this FROM &LIB.FILE2ALIAS AD JOIN &LIB.FILE1ALIAS NM ON (ad.VENDR = Vendorno)

    ------------------------------
    Newton Beckford
    Senior Technical Support Engineer
    Rocket Internal - All Brands
    Mississauga ON CA
    ------------------------------

    Original Message