Problem in using subquery in select statement of a view

[abbasma]

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.

[rareddy]

Can you post the whole error stack? Also attach the query plan. See SET Statement | Teiid Documentation and https://teiid.gitbooks.io/documents/content/reference/Query_Plans.html

[shawkins]

> How can I resolve it ?

It's very hard to say what is happening from just the attached.  The exception is coming from the database, so it could be incorrect sql is being generated or some issue on the database side.  Either way as Ramesh is asking for more information is needed to determine what is wrong.