0

I've got a query that I wrote against the PostgreSQL db working fine for one of the apps I support and want to create it as a report in Mendix. I dropped the query into the OQL editor and made all of the necessary syntax changes and cleared all of the known issues. However, when I run it an error appears that basically says: Caused by: com.mendix.systemwideinterfaces.MendixRuntimeException: Join element contains invalid location: (SELECT CAST(X1.HEADERKEY STRING), CAST(X1.LINEID STRING) FROM OracleEBS.XXCRLPLUSORDERSV AS X1 WHERE X1.ITEM != 'PLUSTXTAX' AND X1.HEADERKEY LIKE '%00') AS B Here is the script if any other experts out there can help me figure out what it doesn't like. I know without the db it's difficult to troubleshoot but I'm hoping someone sees the issue that I can't seem to find. Here's the Mendix version: SELECT ORDERNO as ORDERNO ,DATELASTBILLED as DATELASTBILLED ,ORDERLINENO as ORDERLINENO ,OLSTATUS as OLSTATUS ,T1.ORACLELINEID as ORACLELINEID FROM ( SELECT ORDERNO as ORDERNO ,DATELASTBILLED as DATELASTBILLED ,ORDERLINENO as ORDERLINENO ,OLSTATUS as OLSTATUS ,B.ORACLELINEID as ORACLELINEID FROM ( select DISTINCT cast(O.OrderNumberDisplay as string) as ORDERNO ,cast(O.DateLastBilled as string) as DATELASTBILLED ,cast(OL.OrderLineNumber as string) as ORDERLINENO ,cast(OL.Status as string) as OLSTATUS ,'' as ORACLELINEID from CallCenter."Order" as O inner join O/CallCenter.OrderOrderLine/CallCenter.OrderLine as OL inner join OL/CallCenter.FeeOrderLine/CallCenter.Fee as F where cast(O.isBilled as string) = 'True' and cast(OL.Status as string) = 'Reported' and O.DateLastBilled < '[%BeginOfCurrentDay%]' ) as A INNER JOIN ( SELECT CAST(X1.HEADERKEY as string) as HEADERKEY ,CAST(X1.LINEID as string) as ORACLELINEID FROM OracleEBS.XXCRLPLUSORDERSV as X1 WHERE X1.ITEM != 'PLUSTXTAX' and X1.HEADERKEY LIKE '%00' ) as B on CAST(A.ORDERNO as string) = CAST(B.HEADER_KEY as string) UNION SELECT ORDERNO as ORDERNO ,DATELASTBILLED as DATELASTBILLED ,ORDERLINENO as ORDERLINENO ,OLSTATUS as OLSTATUS ,D.ORACLELINEID as ORACLELINEID FROM ( select DISTINCT cast(O.OrderNumberDisplay as string) as ORDERNO ,cast(O.DateLastBilled as string) as DATELASTBILLED ,cast(OL.OrderLineNumber as string) as ORDERLINENO ,cast(OL.Status as string) as OLSTATUS ,'' as ORACLELINEID from CallCenter."Order" O inner join O/CallCenter.OrderOrderLine/CallCenter.OrderLine as OL where cast(O.isBilled as string) = 'True' and cast(OL.Status as string) = 'Reported' and O.DateLastBilled < '[%BeginOfCurrentDay%]' ) as C INNER JOIN ( SELECT CAST(X2.HEADERKEY as string) as HEADERKEY ,cast(X2.LINEID as string) as ORACLELINEID FROM OracleEBS.XXCRLPLUSORDERSV as X2 WHERE X2.ITEM != 'PLUSTXTAX' and X2.HEADERKEY LIKE '%00' ) as D on CAST(C.ORDERNO as string) = CAST(D.HEADERKEY as string) ) as T1 LEFT OUTER JOIN ( SELECT DISTINCT CAST(X3.HEADERKEY as string) as HEADERKEY ,cast(X3.LINEID as string) as ORACLELINEID FROM OracleEBS.XXCRLPLUSORDERSV as X3 WHERE X3.ITEM != 'PLUSTXTAX' and X3.HEADERKEY LIKE '%00' ) as T2 ON T1.ORDERNO = T2.HEADERKEY and T1.ORACLELINEID = T2.ORACLELINEID WHERE T2.HEADER_KEY IS NOT NULL

asked 2015-10-20

1 answers