ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Dynamic SQL -extremely long Select statement

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Dynamic SQL -extremely long Select statement

    I'm still not convinced that what I'm trying to do is going to work; however, I need to try. We have a business analyst who knows SQL and he has provided me with an extremely long SELECT statement (partially shown below). But the 'WHERE' clause requires that I set up an ambiguous "IN" . I found some posts from 2017 (thank you Birgitta and Tom Liotta!) and realized that I have to use dynamic SQL to do what he wants. I've done this before, but only with really small SELECT statements.

    Originally, I had this - functions and fields and joins (pls ignore the comments):
    PHP Code:
     EXEC SQL
    DECLARE GETDATA CURSOR FOR
    SELECT SAHDR.AMBZTX/*21*/
    (values STYATTRFC(:CURPLANTSAHDR.AMD1CD'FINISH.TYPE')),/*59*/
    LPLLDATE/*9*/
    FG.ALWCNB/*10*/
    FG.ALWONB/*10*/
    (values STYATTRFC(:CURPLANT,SAHDR.AMD1CD,'STYLE.COLOR')) as COLNAME,
    /*68*/
    COLORSEQ/*9*/
    (values PRTATTRFC(:CURPLANT'---',SAHDR.AMBZTX,'ROUTING')), /*60*/
    CONV.CRYECD/*12*/
    CRTSCART/*9*/
    CRTSSLOT/*9*/
    (values STYATTRFC(:CURPLANT,SAHDR.AMD1CD,'DR.BUILD.SEQ')), /*58*/
    (values GETSIZEF(:CURPLANT,'---',SAHDR.AMBZTX,'H')), /*52*/
    (values GETSIZEF(:CURPLANT,'---',SAHDR.AMBZTX,'W')), /*52*/
    (values GETSIZEF(:CURPLANT,'---',SAHDR.AMBZTX,'L')), /*52*/
    RIGHT(LEFT(oh.aelkcd,5),1), /*27*/
    FG.ALWBNB/*10*/
    FG.ALDTCD/*10*/
    LPLBLDSQ/*9*/
    PH.AAW3CD/*10*/
    FIN_CART/*9*/
    FIN_SLOT /*8*/

    FROM /*4*/
    LICPLTEPF /*9*/
    JOIN CARTSLTPF ON
    LPLPLT 
    CRTSPLT AND //char 2, char 2
    LPLACK CRTSACK AND //char 6, char 6
    LPLSUF CRTSSUF AND //numeric 3,0, numeric 3,0
    LPLLNE CRTSLNE AND //numeric 3,0, numeric 3,0
    LPLLPNBR CRTSLPNO //numeric 4,0, numeric 4,0
    JOIN SCFGSCHPF AS FG ON
    LPLPLT
    =ALBVCD AND //char 2, char 2
    LPLACK ALDWCD AND //char 6,
    LPLSUF ALB3NB AND //numeric 3,0, nUMBER 3,0
    LPLLNE ALB4NB //numeric 3,0, Number 3,0
    JOIN ORHEADRPF as OH ON
    LPLPLT 
    AEA4CD AND
    LPLACK AEA5CD AND
    LPLSUF AEA2NB
    JOIN SCSADTLPF ON
    CRTSPLT
    =BQBZCD AND
    CRTSACK BQOFCD AND
    CRTSSUF BQJMNB AND
    CRTSLNE=BQJNNB AND //numeric 3,0, NUMERIC 3,0
    CRTSSCHED BQDRNB //numeric 7,0, numeric 7,0
    JOIN SCSAHDRPF AS SAHDR ON
    BQBZCD
    =AMBZCD AND
    BQDRNB=AMDRNB //numeric 7,0, numeric 7,0

    LEFT JOIN INVPCNVPF CONV ON
    AMBZCD
    =CRYBCD AND
    AMBZTX=CRYDCD //CHAR 15,
    JOIN PRHEADRPF as PH ON
    AMBZCD
    =AAB5CD AND
    AMBZTX=AAAATX AND
    AACLCD='---'

    LEFT JOIN COLSEQPF ON
    COLORNME 
    = :COLNAME

    WHERE LPLRUNDTE 
    = :TodayDate and //numeric 7,0,
    LPLRUNNUM = :RunType and //char 2,
    CARTSLTPF.ROUTE_NBR IN :ROUTELIST
    The dynamic bit is the very last line - :ROUTELIST - if I hard code the route numbers, it works.

    So, the question is - do I need to define a gigantic SQLString to hold that entire thing, or is there a way to just make the WHERE clause dynamic? Does that make sense?

    Now, the BA tells me that if I can get this to work, all I will have to do is calculate the cart and slot numbers and save that back into the CARTSLTPF file! I don't quite believe that yet. I'm really struggling with the specification for this one - it's worse than when I was trying to figure out SFTP!



  • #2
    You can insert the values into a temporary table and use that table in the IN.

    Code:
    CARTSLTPF.ROUTE_NBR IN (SELECT ROUTE FROM QTEMP.ROUTELIST)

    Comment


    • MelissaG
      MelissaG commented
      Editing a comment
      Thank you!!!!! (I use more exclamation points than a teenager ) That was exactly the kick I needed. Just for the record, from here I figured out something else new to me - creating a temporary table directly within my RPG program. The folks on this forum are so wonderful. I hope you all have a great day!
Working...
X