2 Replies Latest reply on Jun 23, 2014 9:47 AM by Steven Hawkins

    OUTER JOIN of XMLTables()

    virtualdatabase Apprentice

      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.

        • 1. Re: OUTER JOIN of XMLTables()
          Ramesh Reddy Master

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

          • 2. Re: OUTER JOIN of XMLTables()
            Steven Hawkins Master

            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.