1 Reply Latest reply on Dec 16, 2014 2:25 PM by rareddy

    mongodb teiid working but can't get embedded structure to work

    afrieden

      Hi all, we have a working mongodb vdb.  I am able to get basic queries working

       

      mongodb-vdb.xml:

                      <metadata type="DDL">
                              <![CDATA[
                                      CREATE FOREIGN TABLE records (
                                      record_id varchar(100) PRIMARY KEY,
                                      position integer,
                                      contig varchar(25),
                                      reference varchar(100),
                                      alternate varchar(100),
                                      variant boolean,
                                      ordered boolean,
                                      version varchar(100),
                                      barcode varchar(100)
                                      ) OPTIONS(UPDATABLE 'FALSE');
      
      
                                      CREATE FOREIGN TABLE sample (
                                      record_id varchar(100) PRIMARY KEY,
                                      name varchar(255),
                                      zygosity varchar(50),
                                      genotype varchar(25),
                                      CL boolean,
                                      DP integer,
                                      FOREIGN KEY (record_id) REFERENCES records (record_id)
                                      ) OPTIONS(UPDATABLE 'FALSE', "teiid_mongo:MERGE" 'records');
                                      ]]>
                      </metadata>
      
      

       

      And I am able to successfully run a simple query:

       

      SELECT * FROM records
      WHERE records.barcode='PATXXX' AND records.position=12345678;
      
      

       

      However, when I try to do a join to the embedded structure:

      SELECT * FROM records
      JOIN sample on records.record_id = sample.record_id
      WHERE records.barcode='PATXXX' AND records.position=12345678;
      
      

       

      This gives the error of

      WARNING: Failed to execute: SELECT * FROM records JOIN sample on records.records_id = sample.records.id WHERE records.barcode='PATXXX' AND records.position=12345678  because: Remote org.teiid.api.exception.query.QueryResolverException: Group specified is ambiguous, resubmit the query by fully qualifying the group name: sample
      org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryResolverException: Group specified is ambiguous, resubmit the query by fully qualifying the group name: sample
      
      

       

      Any thoughts on how to fix this?  Thanks!