×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact
[javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.