2 Replies Latest reply on Apr 1, 2016 9:40 AM by Steven Hawkins

    Problem in using subquery in select statement of a view

    Mamta Abbas Newbie
    Mamta Abbas Mar 31, 2016 10:44 AM

      I have a vdb with a view, which is based on multiple tables joined using inner or outer join.

      I have attached the ddl statement from the VDB.testPDMDim.ddl

      When I try to run a select statement with subquery such as :

      " SELECT OIL FROM PRODUCTION_VOLUME AS X WHERE X.VOLUME_DATE = (SELECT MAX(VOLUME_DATE) FROM PRODUCTION_VOLUME AS Y WHERE X.ENTITY_ID = Y.ENTITY_ID )"

      it fails and I get below error " [Error Code: 30504, SQL State: 50000]  TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 PDMDimSrc: 904 TEIID11008:TEIID11004 Error executing statement(s)"

      I have attached the full log from the execution.teiidError.txt

      Such kind of subquery works fine if the view is simple with no joins.

      I understand the issue might be with joins.

      How can I resolve it ?

      Thanks in advance.