GM, I am writing a SQLRPGLE program that will use DB2 SQL to generate XML files in the IFS. I was able to test the theory using standard SQL (select....) and generate the XML file in the IFS using values..into.
When I tried to expand this using CTE's with the values..into, I get a syntax error
SQL0104 Token WITH was not valid. Valid tokens: (
My question is OK to use CTE's with Values..into? and if so, what am I doing wrong? (code below). Also. I know there are other, maybe more suitable ways to do this. But this was something I recently came across and wanted to try it out to see if it is some thing to add to list of techniques. But with that error creeping in, I may need to move on to something else...
exec sql VALUES(
With Stylemaster as (
SELECT CPY007, DIV007, SEY007, KYS007, CC#007, SST007, STD007,
FAB007, LEN007, SCT007, CGP007, DLV007, RPR007, PC1007, PC3007,
STS007, DCD007, SDA007, SDB007, SDC007, SDD007, SDE007, SDF007,
SDG007, PI1007, PI2007, PI3007, PI4007, PI5007, PI6007, PI7007,
PI8007, CDEC07, SCALE, INVALID, IDX, COLOR, SKU100, SKU200,
SKU300, SKU400,CPY, SINDEX, SZS, DES, S01, P01, N01, B01, ASZ, AS2
FROM ((SELECT CPY007, DIV007, SEY007, KYS007, CC#007, SST007, STD007,
FAB007, LEN007, SCT007, CGP007, DLV007, RPR007, PC1007, PC3007,
STS007, DCD007, SDA007, SDB007, SDC007, SDD007, SDE007, SDF007,
SDG007, PI1007, PI2007, PI3007, PI4007, PI5007, PI6007, PI7007,
PI8007, CDEC07, SCALE, INVALID, IDX, COLOR
FROM DRLMFILES.pst007 t,
LATERAL (VALUES (SSC007, SUBSTR(SZO007, 01, 1), 01),
(SSC007, SUBSTR(SZO007, 02, 1), 02),
(SSC007, SUBSTR(SZO007, 03, 1), 03),
(SSC007, SUBSTR(SZO007, 04, 1), 04),
(SSC007, SUBSTR(SZO007, 05, 1), 05),
(SSC007, SUBSTR(SZO007, 06, 1), 06),
(SSC007, SUBSTR(SZO007, 07, 1), 07),
(SSC007, SUBSTR(SZO007, 08, 1), 08),
(SSC007, SUBSTR(SZO007, 09, 1), 09),
(SSC007, SUBSTR(SZO007, 10, 1), 10),
(SSD007, SUBSTR(SZO007, 11, 1), 01),
(SSD007, SUBSTR(SZO007, 12, 1), 02),
(SSD007, SUBSTR(SZO007, 13, 1), 03),
(SSD007, SUBSTR(SZO007, 14, 1), 04),
(SSD007, SUBSTR(SZO007, 15, 1), 05),
(SSD007, SUBSTR(SZO007, 16, 1), 06),
(SSD007, SUBSTR(SZO007, 17, 1), 07),
(SSD007, SUBSTR(SZO007, 18, 1), 08),
(SSD007, SUBSTR(SZO007, 19, 1), 09),
(SSD007, SUBSTR(SZO007, 20, 1), 10),
(SSE007, SUBSTR(SZO007, 21, 1), 01),
(SSE007, SUBSTR(SZO007, 22, 1), 02),
(SSE007, SUBSTR(SZO007, 23, 1), 03),
(SSE007, SUBSTR(SZO007, 24, 1), 04),
(SSE007, SUBSTR(SZO007, 25, 1), 05),
(SSE007, SUBSTR(SZO007, 26, 1), 06),
(SSE007, SUBSTR(SZO007, 27, 1), 07),
(SSE007, SUBSTR(SZO007, 28, 1), 08),
(SSE007, SUBSTR(SZO007, 29, 1), 09),
(SSE007, SUBSTR(SZO007, 30, 1), 10))
AS D(SCALE, INVALID, IDX),
LATERAL (VALUES (CL0107),(CL0207),(CL0307),(CL0407),(CL0507),
(CL0607),(CL0707),(CL0807),(CL0907),(CL1007),
(CL1107),(CL1207),(CL1307),(CL1407),(CL1507),
(CL1607),(CL1707),(CL1807),(CL1907),(CL2007),
(CL2107),(CL2207),(CL2307),(CL2407),(CL2507),
(CL2607),(CL2707),(CL2807),(CL2907),(CL3007),
(CL3607),(CL3707),(CL3807),(CL3907),(CL4007),
(LEN007))
AS E(COLOR)
WHERE SCALE <> ' ' AND INVALID <> 'X' AND COLOR <> ' ') S
JOIN (SELECT CPY, SZS, DES, SINDEX, S01, P01, N01, B01, ASZ, AS2
FROM DRLMFILES.PGI538 T,
LATERAL (VALUES (CPY538, 01, SZS538, DES538, S01538,
P01538, N01538, B01538, ASZ538, AS2538),
(CPY538, 02, SZS538, DES538, S02538,
P02538, N02538, B02538, ASZ538, AS2538),
(CPY538, 03, SZS538, DES538, S03538,
P03538, N03538, B03538, ASZ538, AS2538),
(CPY538, 04, SZS538, DES538, S04538,
P04538, N04538, B04538, ASZ538, AS2538),
(CPY538, 05, SZS538, DES538, S05538,
P05538, N05538, B05538, ASZ538, AS2538),
(CPY538, 06, SZS538, DES538, S06538,
P06538, N06538, B06538, ASZ538, AS2538),
(CPY538, 07, SZS538, DES538, S07538,
P07538, N07538, B07538, ASZ538, AS2538),
(CPY538, 08, SZS538, DES538, S08538,
P08538, N08538, B08538, ASZ538, AS2538),
(CPY538, 09, SZS538, DES538, S09538,
P09538, N09538, B09538, ASZ538, AS2538),
(CPY538, 10, SZS538, DES538, S10538,
P10538, N10538, B10538, ASZ538, AS2538))
AS STYLE(CPY, SINDEX, SZS, DES, S01, P01, N01, B01, ASZ, AS2)
WHERE S01 <> ' '
ORDER BY CPY, SINDEX, SZS, DES, S01, P01, N01, B01, ASZ, AS2)
SC ON S.CPY007 = SC.CPY AND S.SCALE = SC.SZS AND S.IDX = SC.SINDEX)
JOIN TRLMFILES.PDIV000 DD on S.CPY007 = COMP00 and S.DIV007 = DIVI00
GROUP BY CPY007, DIV007, SEY007, KYS007, CC#007, SST007, STD007,
FAB007, LEN007, SCT007, CGP007, DLV007, RPR007, PC1007, PC3007, STS007,
DCD007, SDA007, SDB007, SDC007, SDD007, SDE007, SDF007, SDG007, PI1007,
SZS, PI2007, PI3007, PI4007, PI5007, PI6007, PI7007, PI8007, CDEC07,
SCALE, INVALID, IDX, COLOR, S01, SKU100, SKU200, SKU300, SKU400, CPY,
SINDEX, SZS, DES, S01, P01, N01, B01, ASZ, AS2
ORDER BY CPY007, DIV007, SEY007, KYS007, CC#007, SST007, STD007,
FAB007, LEN007, SCT007, CGP007, DLV007, RPR007, PC1007, PC3007, STS007,
DCD007, SDA007, SDB007, SDC007, SDD007, SDE007, SDF007, SDG007, PI1007,
SZS, PI2007, PI3007, PI4007, PI5007, PI6007, PI7007, PI8007, CDEC07,
SCALE, INVALID, IDX, COLOR, S01),
UPCMaster as
(SELECT (DIGITS(INF004) ||
DIGITS(MFG004) ||
DIGITS(UPC004) ||
DIGITS(CKD004)) as UPC,
SKU004 ,
FAB004 as Fabric,
LEN004 as Length,
SIZ004 as Size,
CASE WHEN SIZ004 = 'PPK' THEN LEN004
WHEN FAB004 = 'PR' THEN LEN004
WHEN FAB004 = 'EA' THEN LEN004
ELSE CLR004
END as ColorCode,
SSC004 as Sizescale,
IDX004 as Index from DRLMFILES.PUP004
WHERE SKU004 =INT(2307502))
SELECT
XMLElement(name "Style",
XMLAttributes(CPY007 as Company,
DIV007 as Division,
CC#007 as SKU_number,
Color as Color ),
XMLForest(CPY007 as Company,
DIV007 as Division,
SEY007 as Seasonyear,
KYS007 as Season,
CC#007 as SKU_number,
SST007 as Style,
STD007 as Style_desc,
FAB007 as Fabric,
LEN007 as Length,
CGP007 as Group_Code,
DLV007 as Delv_code,
RPR007 as Sugretprc,
PC1007 as standprice,
PC3007 as euro_cosdt,
STS007 as pack_hang,
DCD007 as style_dsc,
SDA007 as styledesc2,
SDB007 as styledesc3,
SDC007 as styledesc4,
SDD007 as styledesc5,
SDE007 as styledesc6,
SDF007 as styledesc7,
SDG007 as styledesc8,
PI1007 as footupper,
PI2007 as footsole,
PI3007 as htscode,
PI4007 as packinstr4,
PI5007 as packinstr5,
PI6007 as packinstr6,
PI7007 as packinstr7,
PI8007 as packinstr8,
CDEC07 as citesstyle,
XMLAGG( XMLELEMENT(name UPCNUMBER,
XMLForest(Index,
UPC,
Fabric,
Length,
Size,
ColorCode ))
ORDER BY UPC, SIZE) as "UPCS" ))
FROM StyleMaster AAA join UPCMaster BBB on
CC#007 = SKU004 and
Color = ColorCode and
SZS = SizeScale and IDX = Index
GROUP BY CPY007, DIV007, SEY007, KYS007, CC#007, SST007, STD007,
FAB007, LEN007, SCT007, CGP007, DLV007, RPR007, PC1007,
PC3007, STS007, DCD007, SDA007, SDB007, SDC007, SDD007,
SDE007, SDF007, SDG007, PI1007, PI2007, PI3007, PI4007,
PI5007, PI6007, PI7007, PI8007, CDEC07, COLOR
ORDER BY CPY007, DIV007, SEY007, KYS007, CC#007, SST007, STD007,
FAB007, LEN007, SCT007, CGP007, DLV007, RPR007, PC1007,
PC3007, STS007, DCD007, SDA007, SDB007, SDC007, SDD007,
SDE007, SDF007, SDG007, PI1007, PI2007, PI3007, PI4007,
PI5007, PI6007, PI7007, PI8007, CDEC07, COLOR)
INTO :Outfile ;
Next time, consider putting together a simpler version that demonstrates the issue...
exec sql
values (with cte as (select current_timestamp(8)
from sysibm.sysdummy1
)
select * from cte
)
into :ts;
Same error, which surprises me...but reading the documentation for VALUES INTO it does say...
row-fullselect
A fullselect that returns a single result row. The result column values are assigned to each corresponding variable. If the result of the fullselect is no rows, then null values are assigned. An error is returned if there is more than one row in the result.
A fullselect is not the same as a select-statement which allows CTEs per this page
fullselect
The fullselect is a component of the select-statement
Consider building a view for the select-statement then your embedded code becomes simply:
exec sql
values (select * from myview) into :outfile;