Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 210 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 77 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Master Detail Report - XML output is incorrect.
Summary
Master Detail Report - XML output is incorrect.
Content
Hello,
I have the following contents in the datatemplate.xml
<?xml version="1.0" encoding="UTF-8"?><dataTemplate name="PRODS" defaultPackage="" version="1.0"> <parameters> <parameter name="p_prod" dataType="character" /> </parameters> <dataQuery> <sqlStatement name="Q_MAST"><![CDATA[SELECT product_id, prod_seq, product_name FROM prod_mast WHERE product_id=:p_prod]]></sqlStatement> <sqlStatement name="Q_DET"><![CDATA[ SELECT product_id,prod_det_seq,product_detail,prod_mast_seq FROM prod_det WHERE product_id=:p_prod ]]></sqlStatement></dataQuery> <dataStructure> <group name="G_MAST" source="Q_MAST"> <element name="product_id" value="product_id" /> <element name="prod_seq" value="prod_seq" /> <element name="product_name" value="product_name" /> <group name="G_DET" source="Q_DET"> <element name="product_id" value="product_id" /> <element name="prod_det_seq" value="prod_det_seq" /> <element name="product_detail" value="product_detail" /> <element name="prod_mast_seq" value="prod_mast_seq" /> </group> </group> </dataStructure></dataTemplate>
The XML output generated based on the above is as follows
<?xml version="1.0" encoding="UTF-8"?><PRODS> <LIST_G_MAST> <G_MAST> <PRODUCT_ID>SB1002</PRODUCT_ID> <PROD_SEQ>1002</PROD_SEQ> <PRODUCT_NAME>Square Boxes</PRODUCT_NAME> <LIST_G_DET> <G_DET> <PRODUCT_ID>SB1002</PRODUCT_ID> <PROD_DET_SEQ>1002.1</PROD_DET_SEQ> <PRODUCT_DETAIL>12 inch box</PRODUCT_DETAIL> <PROD_MAST_SEQ>1002</PROD_MAST_SEQ> </G_DET> <G_DET> <PRODUCT_ID>SB1002</PRODUCT_ID> <PROD_DET_SEQ>1002.2</PROD_DET_SEQ> <PRODUCT_DETAIL>24 inch box</PRODUCT_DETAIL> <PROD_MAST_SEQ>1002</PROD_MAST_SEQ> </G_DET> <G_DET> <PRODUCT_ID>SB2006</PRODUCT_ID> <PROD_DET_SEQ>2006.1</PROD_DET_SEQ> <PRODUCT_DETAIL>Small size box</PRODUCT_DETAIL> <PROD_MAST_SEQ>2006</PROD_MAST_SEQ> </G_DET> <G_DET> <PRODUCT_ID>SB2006</PRODUCT_ID> <PROD_DET_SEQ>2006.2</PROD_DET_SEQ> <PRODUCT_DETAIL>Medium size box</PRODUCT_DETAIL> <PROD_MAST_SEQ>2006</PROD_MAST_SEQ> </G_DET> </LIST_G_DET> </G_MAST> <G_MAST> <PRODUCT_ID>SB2006</PRODUCT_ID> <PROD_SEQ>2006</PROD_SEQ> <PRODUCT_NAME>Misc. Boxes</PRODUCT_NAME> <LIST_G_DET> <G_DET> <PRODUCT_ID>SB1002</PRODUCT_ID> <PROD_DET_SEQ>1002.1</PROD_DET_SEQ> <PRODUCT_DETAIL>12 inch box</PRODUCT_DETAIL> <PROD_MAST_SEQ>1002</PROD_MAST_SEQ> </G_DET> <G_DET> <PRODUCT_ID>SB1002</PRODUCT_ID> <PROD_DET_SEQ>1002.2</PROD_DET_SEQ> <PRODUCT_DETAIL>24 inch box</PRODUCT_DETAIL> <PROD_MAST_SEQ>1002</PROD_MAST_SEQ> </G_DET> <G_DET> <PRODUCT_ID>SB2006</PRODUCT_ID> <PROD_DET_SEQ>2006.1</PROD_DET_SEQ> <PRODUCT_DETAIL>Small size box</PRODUCT_DETAIL> <PROD_MAST_SEQ>2006</PROD_MAST_SEQ> </G_DET> <G_DET> <PRODUCT_ID>SB2006</PRODUCT_ID> <PROD_DET_SEQ>2006.2</PROD_DET_SEQ> <PRODUCT_DETAIL>Medium size box</PRODUCT_DETAIL> <PROD_MAST_SEQ>2006</PROD_MAST_SEQ> </G_DET> </LIST_G_DET> </G_MAST> </LIST_G_MAST></PRODS>
The output is what is not what is expected,
Ideally, I would like to have the detail for the corresponding or related PROD_SEQ, the expected XML output is as follows
<?xml version="1.0" encoding="UTF-8"?>
<PRODS>
<LIST_G_MAST>
<G_MAST>
<PRODUCT_ID>SB1002</PRODUCT_ID>
<PROD_SEQ>1002</PROD_SEQ>
<PRODUCT_NAME>Square Boxes</PRODUCT_NAME>
<LIST_G_DET>
<G_DET>
<PRODUCT_ID>SB1002</PRODUCT_ID>
<PROD_DET_SEQ>1002.1</PROD_DET_SEQ>
<PRODUCT_DETAIL>12 inch box</PRODUCT_DETAIL>
<PROD_MAST_SEQ>1002</PROD_MAST_SEQ>
</G_DET>
<G_DET>
<PRODUCT_ID>SB1002</PRODUCT_ID>
<PROD_DET_SEQ>1002.2</PROD_DET_SEQ>
<PRODUCT_DETAIL>24 inch box</PRODUCT_DETAIL>
<PROD_MAST_SEQ>1002</PROD_MAST_SEQ>
</G_DET>
</LIST_G_DET>
</G_MAST>
<G_MAST>
<PRODUCT_ID>SB2006</PRODUCT_ID>
<PROD_SEQ>2006</PROD_SEQ>
<PRODUCT_NAME>Misc. Boxes</PRODUCT_NAME>
<LIST_G_DET>
<G_DET>
<PRODUCT_ID>SB2006</PRODUCT_ID>
<PROD_DET_SEQ>2006.1</PROD_DET_SEQ>
<PRODUCT_DETAIL>Small size box</PRODUCT_DETAIL>
<PROD_MAST_SEQ>2006</PROD_MAST_SEQ>
</G_DET>
<G_DET>
<PRODUCT_ID>SB2006</PRODUCT_ID>
<PROD_DET_SEQ>2006.2</PROD_DET_SEQ>
<PRODUCT_DETAIL>Medium size box</PRODUCT_DETAIL>
<PROD_MAST_SEQ>2006</PROD_MAST_SEQ>
</G_DET>
</LIST_G_DET>
</G_MAST>
</LIST_G_MAST>
</PRODS>
How can I achieve the above output?
Appreciate any insight
Answers
-
I think prod_mast_seq in prod_det contains the proj_seq of master table so you can do the below
<dataQuery>
<sqlStatement name="Q_MAST"><![CDATA[SELECT product_id, prod_seq, product_name FROM prod_mast
WHERE product_id=:p_prod]]></sqlStatement>
<sqlStatement name="Q_DET"><![CDATA[
SELECT product_id,prod_det_seq,product_detail,prod_mast_seq FROM prod_det WHERE product_id=:product_id and prod_mast_seq = :prod_seq
]]></sqlStatement>
</dataQuery>
0 -
Can you just copy paste your dataQuery only?
0 -
Do you have prod_seq column in table prod_det?
If you have you can pass the prod_seq from the parent sql to child sql as below. This will restrict child sql to pick the value related to parent sql
<dataQuery>
<sqlStatement name="Q_MAST"><![CDATA[SELECT product_id, prod_seq, product_name FROM prod_mast
WHERE product_id=:p_prod]]></sqlStatement>
<sqlStatement name="Q_DET"><![CDATA[
SELECT product_id,prod_det_seq,product_detail,prod_mast_seq FROM prod_det WHERE product_id=:p_prod and prod_seq = :prod_seq
]]></sqlStatement>
</dataQuery>
0 -
I am actual dataQuery is different from what I had posted in the question as the question contain pseudo code, replica of my original.
Can I email you my original dataQuery if this is not an intrusion?
0 -
I reckon that bind variable should not be passed as a parameter.0 -
Thanks for the update.
When I tried to provide the bind variable in the detail query, I am not getting the details in the XML output, only master information I appearing.
Regards
0