[JBoss JIRA] (TEIID-4998) Query with the XML functions can't be evaluated because outer selects aren't visible for the inner statements.
9:37 a.m.
[
https://issues.jboss.org/browse/TEIID-4998?page=com.atlassian.jira.plugin...
]
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)
3195
days inactive
3195
days old
0 comments
1 participants
participants (1)
-
Debbie Steigner (JIRA)