OUTER JOIN of XMLTables()

[virtualdatabase]

I'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.

[rareddy]

I do not think that is valid SQL. There will be more than single value of A1.ID, and join is not going issue in nested loop multiple queries in this case.

Where as A.ID case, in the nested table syntax you can only access variables from previous tables not post tables. For example "select * from (invokeHTTP) as a , XMLTable as b), in b you can access a, not vice versa.

Ramesh..

[shawkins]

It looks like you are trying to do a side-ways join.  That requires the TABLE keyword when not directly joining against a nested table:

select ... from a join TABLE(select ... a.x) as b ...

However you cannot do a side-ways join with a right or full outer join.