[teiid-issues] [JBoss JIRA] (TEIID-4998) Query with the XML functions can't be evaluated because outer selects aren't visible for the inner statements.

[Debbie Steigner (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13437666#comment-13437666 ] 

Debbie Steigner commented on TEIID-4998:
----------------------------------------

The below transformation should work:

BEGIN
SELECT XMLELEMENT(NAME root, XMLAGG(XMLELEMENT(NAME "order", XMLELEMENT(NAME id, OrderID),XMLELEMENT(NAME "date",OrderDate),XMLELEMENT(NAME details,(SELECT XMLAGG(XMLELEMENT(NAME detail, XMLELEMENT(NAME unitprice, UnitPrice), XMLELEMENT(NAME quantity,Quantity), XMLELEMENT(NAME products,(SELECT XMLAGG(XMLELEMENT(NAME product, XMLELEMENT(NAME name, ProductName), XMLELEMENT(NAME quantityPerUnit, QuantityPerUnit), XMLELEMENT(NAME inStock,UnitsinStock))) FROM northwind.products as prd where odl.ProductID = ProductID)))) from northwind.order_details as odl where OrderID = odr.OrderID))))) as xml_out from northwind.orders as odr where OrderID IN (10248);
END

I also tried with XMLFOREST, but received the same error:

BEGIN
SELECT XMLELEMENT(NAME Orders,
  XMLAGG(
	XMLELEMENT(NAME "order", 
	XMLFOREST(
		OrderID as "id",
		OrderDate as "date",
		(SELECT XMLAGG(
				XMLELEMENT(NAME Detail,
				XMLFOREST(
					UnitPrice as "unitprice",
					Quantity as "quantity",
(SELECT XMLAGG(
		XMLELEMENT(NAME Product, 
		XMLFOREST(
			ProductName as "name",
			QuantityPerUnit as "quantityperUnit",
			UnitsinStock as "inStock"
)
)
)
FROM northwind.products as prd where odl.ProductID = ProductID
) AS Products
)
)
)
FROM northwind.order_details as odl where OrderID = odr.OrderID
) AS Details
)
)
)
) as xml_out from northwind.orders as odr where OrderID IN (10248);
END

If i remove all the nested xmlaggs and use a view table that joins the 3 tables together:

BEGIN
	SELECT XMLELEMENT(NAME orders, XMLAGG(XMLELEMENT(NAME "order", XMLELEMENT(NAME id, nwrest.allorders.OrderID), XMLELEMENT(NAME "date", nwrest.allorders.OrderDate), XMLELEMENT(NAME unitprice, nwrest.allorders.UnitPrice), XMLELEMENT(NAME quantity, nwrest.allorders.Quantity), XMLELEMENT(NAME name, nwrest.allorders.ProductName), XMLELEMENT(NAME quantityPerUnit, nwrest.allorders.QuantityPerUnit), XMLELEMENT(NAME inStock, nwrest.allorders.UnitsInStock)))) AS xml_out FROM nwrest.allorders WHERE nwrest.allorders.OrderID = 10248;
END

It works but doesn't give the xml structure the customer is looking for:

<orders>
<order>
<id>10248</id>
<date>1996-07-04T05:00:00Z</date>
<unitprice>14</unitprice>
<quantity>12</quantity>
<name>Queso Cabrales</name>
<quantityPerUnit>1 kg pkg.</quantityPerUnit>
<inStock>22</inStock>
</order>
<order>
<id>10248</id>
<date>1996-07-04T05:00:00Z</date>
<unitprice>9.8</unitprice>
<quantity>10</quantity>
<name>Singaporean Hokkien Fried Mee</name>
<quantityPerUnit>32 - 1 kg pkgs.</quantityPerUnit>
<inStock>26</inStock>
</order>
<order>
<id>10248</id>
<date>1996-07-04T05:00:00Z</date>
<unitprice>34.8</unitprice>
<quantity>5</quantity>
<name>Mozzarella di Giovanni</name>
<quantityPerUnit>24 - 200 g pkgs.</quantityPerUnit>
<inStock>14</inStock>
</order>
</orders>

> Query with the XML functions can't be evaluated because outer selects aren't visible for the inner statements.
> --------------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-4998
>                 URL: https://issues.jboss.org/browse/TEIID-4998
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.12.11.6_3
>            Reporter: Colin Mondesir
>            Assignee: Steven Hawkins
>
> When creating an XML structure with the XML  (XMLELEMENT,XMLAGG, etc.) functions and select for selecting the data from the view models, the query in the procedure can't be evaluated because the outer selects aren't visible for the inner statements.
> org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidComponentException: TEIID30328 Unable to evaluate odr.OrderID: No value was available



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)