First of all many thanks to Birgitta Hauser, Mike Cain, and
Nick Lawrence for all of their excellent articles.
I am writing an EDI x12 850 PO program to write XML to the
IFS. All is going well until I get down to writing the detail PO1
segments. All of the detail files (8 of them) have an extra Line# in the
key field. When I include it I get a SQL0811 error. When I exclude
it I get all 14 rows of detail written out to the XML file.
Again, the problem with the code below is with D01.LCNT in
there I get SQL0811, without it I can’t connect to the rest of my detail files;
like PID, REF, N9, MSG and DTM.
D
MYXMLDOC
S
SQLTYPE(XML_CLOB_FILE)
d
MsgDta
s 52a
d
ts
s
z inz
EXEC SQL SET OPTION
COMMIT=*NONE, DATFMT=*ISO, CLOSQLCSR=*ENDACTGRP;
ts = %timestamp();
CLEAR MYXMLDOC;
MYXMLDOC_NAME =
'/edi/po_' + %char(ts) + '.xml';
MYXMLDOC_NL =
%LEN(%TRIM(MYXMLDOC_NAME));
MyXMLDoc_FO = SQFOVR;
//Replace if exists
Exec SQL
WITH
D01 AS (
SELECT D01.TPID,D01.PO#,D01.REL,D01.CTL#,D01.MBX,D01.LCNT, -- does not with LCNT
XMLGROUP(case when PO101 <> ' ' then PO101 end as
"PO101",
case when PO102 <> 0 then PO102
end as "PO102",
case when PO414 <> 0 then PO414 end as
"PO414"
ORDER BY D01.TPID,D01.PO#,D01.REL,D01.CTL#,D01.MBX,D01.LCNT
OPTION ROW "PO1"
ROOT "detail" )
AS detail
FROM EDI850DP100 D01
GROUP BY D01.TPID,D01.PO#,D01.REL,D01.CTL#,D01.MBX,D01.LCNT )
,
H04 AS (
SELECT H04.TPID,H04.PO#,H04.REL,H04.CTL#,H04.MBX, -- secondary header file with multiple rows
XMLGROUP(DTM01 AS "DTM01",
DTM02 AS "DTM02"
ORDER BY H04.TPID,H04.PO#,H04.REL,H04.CTL#,H04.MBX
OPTION ROW "Date"
ROOT "DTM" )
AS DTM
FROM EDI850HP400 H04
GROUP
BY H04.TPID,H04.PO#,H04.REL,H04.CTL#,H04.MBX )
SELECT XMLDOCUMENT(
XMLELEMENT(NAME "order",
XMLFOREST(H01.TPID AS "TPID",
H01.PO# AS "PO",
H04.DTM AS "DTM",
XMLFOREST(
D01.detail AS "detail"
) AS order_detail )))
AS order_doc
into :MyXMLDoc
FROM EDI850HP100 H01
left outer join H04 on H04.TPID = H01.TPID
and H04.PO# = H01.PO#
and H04.REL = H01.REL
and H04.CTL# = H01.CTL#
and H04.MBX = H01.MBX
left outer join D01 on D01.TPID = H01.TPID
and D01.PO# = H01.PO#
and
D01.REL = H01.REL
and D01.CTL# = H01.CTL#
and D01.MBX = H01.MBX
;
If SQLCODE
<> *Zeros;
EXEC SQL
Get Diagnostics Condition 1 :MsgDta = MESSAGE_TEXT;
Dsply
MsgDta;
else;
Dsply 'XML
Document generated';
EndIf;
*InLR = *On;
Run results;DSPLY Result of SELECT more than one row.
<?xml version="1.0"
encoding="UTF-8"?>
<order><TPID>abc123
</TPID><PO>0000249343899002
</PO><DTM><DTM><Date><DTM01>037</DTM01><DTM02>20120910</DTM02></Date><Date><DTM01>038</DTM01><DTM02>20120915</DTM02></Date><Date><DTM01>063</DTM01><DTM02>20120917</DTM02></Date><Date><DTM01>064</DTM01><DTM02>20120910</DTM02></Date></DTM></DTM><ORDER_DETAIL><detail><detail><PO1><PO101>6
</PO101><PO102>2880</PO102></PO1></detail></detail></ORDER_DETAIL></order>
The DTM wrote out 4 rows but the
PO1 failed after the first one (and it’s line #6 not one???).!
After removing the D01.LCNT
I get all 14 PO1 rows;
DSPLY XML Document
generated
<?xml version="1.0"
encoding="UTF-8"?>
<order><TPID>DOLLAR
TREE KY </TPID><PO>0000249343899002
</PO><DTM><DTM><Date><DTM01>037</DTM01><DTM02>20120910</DTM02></Date><Date><DTM01>038</DTM01><DTM02>20120915</DTM02></Date><Date><DTM01>063</DTM01><DTM02>20120917</DTM02></Date><Date><DTM01>064</DTM01><DTM02>20120910</DTM02></Date></DTM></DTM><ORDER_DETAIL><detail><detail><PO1><PO101>1
</PO101><PO102>4800</PO102></PO1><PO1><PO101>2
</PO101><PO102>7200</PO102></PO1><PO1><PO101>3
</PO101><PO102>2400</PO102></PO1><PO1><PO101>4
</PO101><PO102>4320</PO102></PO1><PO1><PO101>5
</PO101><PO102>1200</PO102></PO1><PO1><PO101>6
</PO101><PO102>2880</PO102></PO1><PO1><PO101>7
</PO101><PO102>4800</PO102></PO1><PO1><PO101>8
</PO101><PO102>4320</PO102></PO1><PO1><PO101>9
</PO101><PO102>3600</PO102></PO1><PO1><PO101>10
</PO101><PO102>6000</PO102></PO1><PO1><PO101>11
</PO101><PO102>2400</PO102></PO1><PO1><PO101>12
</PO101><PO102>7200</PO102></PO1><PO1><PO101>13
</PO101><PO102>4320</PO102></PO1><PO1><PO101>14
</PO101><PO102>21600</PO102></PO1></detail></detail></ORDER_DETAIL></order>
Regards,
Guy Henza
midrange.com
