OUTER JOIN of XMLTables()
virtualdatabase Jun 18, 2014 1:47 PMI've tried a number of variations...
This is the latest version (working and not)
THIS WORKS (though hard-coded)
Select a1.ID, a1.Name, b1.MetaData
from (
SELECT a.*
FROM (EXEC SemaphoreTermsSource.invokeHttp('GET', null, 'http://sesdev.foobar.com/ses?TBDB=UberModel&TEMPLATE=service.xml&SERVICE=conceptmap&expand_hierarchy=1&QUERY=Site%20Name', 'TRUE')) AS f,
XMLTABLE('/SEMAPHORE/TERMS/TERM/HIERARCHY/FIELD' PASSING XMLPARSE(DOCUMENT f.result) COLUMNS NAME STRING PATH '/.' , ID STRING PATH '@ID') AS A
) a1 RIGHT OUTER JOIN
(
SELECT a.*
FROM (EXEC SemaphoreTermsSource.invokeHttp('GET', null, 'http://sesdev.FOOBAR.com/ses/UberModel/terms/582705391944462445282684592317325.xml', 'TRUE')) AS f,
XMLTABLE('/SEMAPHORE/TERMS/TERM/METADATA/FIELD' PASSING XMLPARSE(DOCUMENT f.result) COLUMNS MetaData STRING PATH '/.', ID STRING PATH '../../ID/.') AS A
WHERE MetaData not in ('' ,'Low') ) b1 on a1.id = b1.id
THIS DOES NOT WORK.
Select a1.ID, a1.Name, b1.MetaData
from (
SELECT a.*
FROM (EXEC SemaphoreTermsSource.invokeHttp('GET', null, 'http://sesdev.FOOBAR.com/ses?TBDB=UberModel&TEMPLATE=service.xml&SERVICE=conceptmap&expand_hierarchy=1&QUERY=Site%20Name', 'TRUE')) AS f,
XMLTABLE('/SEMAPHORE/TERMS/TERM/HIERARCHY/FIELD' PASSING XMLPARSE(DOCUMENT f.result) COLUMNS NAME STRING PATH '/.' , ID STRING PATH '@ID') AS A
) a1 FULL OUTER JOIN
(
SELECT a.*
FROM (EXEC SemaphoreTermsSource.invokeHttp('GET', null, 'http://sesdev.FOOBAR.com/ses/UberModel/terms/'||A1.ID||'.xml', 'TRUE')) AS f,
XMLTABLE('/SEMAPHORE/TERMS/TERM/METADATA/FIELD' PASSING XMLPARSE(DOCUMENT f.result) COLUMNS MetaData STRING PATH '/.', ID STRING PATH '../../ID/.') AS A
WHERE MetaData not in ('' ,'Low') ) b1 on a1.id = b1.id
FAILS WITH:
Error: Remote org.teiid.api.exception.query.QueryResolverException: TEIID31119 Symbol A1.ID is specified with an unknown group context
OR
Error: Remote org.teiid.api.exception.query.QueryResolverException: TEIID31119 Symbol A.ID is specified with an unknown group context
SQLState: 50000
(If I replace A1 with A)
Any thoughts/suggestions?
In Essence I need to take find the output from one to use as input to another ws call.