It sounds like you changed the grouping of your query, and are now trying
to insert more than one row into a host variable (or in this case a file).
XMLGROUP is an aggregate function, the resulting XML value is a single
value that represents all rows in the grouping.
If you group by D01.LCNT, then you get one group per LCNT value (more
groups/rows). If you do not group by this column, then it is impossible to
represent the column value for all rows in the group as a single value
without using an aggregate function. (e.g. MIN, MAX, AVG, etc)
You may want to do the join (which involves LCNT) first, and then perform
grouping on the join result.
For easier debug, you might want to run the query using run sql scripts or
IBM Data Studio to make sure you have the query written correctly, and
only then write the RPG program.
The SQL/XML publishing functions are good news in that they push down the
work and optimization of producing XML into DB2...but they also require
education and practice to master.
There are some SQL workshops available for training that might help with
these kinds of queries.
http://www-03.ibm.com/systems/power/software/i/db2/education/advanced-sql.html
Nick Lawrence
DB2 for IBM i
Yesterday we pioneered for today; today, we are pioneering for tomorrow.
- Thomas J. Watson, Sr.
From: rpg400-l-request@xxxxxxxxxxxx
To: rpg400-l@xxxxxxxxxxxx
Date: 02/09/2015 01:02 PM
Subject: RPG400-L Digest, Vol 14, Issue 58
Sent by: "RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx>
message: 1
date: Mon, 9 Feb 2015 08:56:40 -0500
from: Guy Henza <guyhenza@xxxxxxxxxxx>
subject: SQLRPGILE writing XML to the IFS getting a SQL0811 error
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
