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):
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!
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(:CURPLANT, SAHDR.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;
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!
Comment