Does anyone have a fix or work around for this problem?
_____ From: Michael Irving [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 12:28 PM To: 'dev@ofbiz.apache.org' Subject: Errors: findByAnd with Oracle DB When using findByAnd against an entity-view with an Oracle backend, the query fails. Example: delegator .findByAnd ( "PartyNoteView" , UtilMisc .toMap ( "targetPartyId" , partyId ) , UtilMisc .toList ( "-noteDateTime" ) ) OFBIz generates the following SQL: SELECT PN.PARTY_ID AS TARGET_PARTY_ID, ND.NOTE_ID AS NOTE_ID, ND.NOTE_NAME AS NOTE_NAME, ND.NOTE_INFO AS NOTE_INFO, ND.NOTE_DATE_TIME AS NOTE_DATE_TIME, ND.NOTE_PARTY AS NOTE_PARTY FROM OFBIZ_NEW.PARTY_NOTE PN INNER JOIN OFBIZ_NEW.NOTE_DATA ND ON PN.NOTE_ID = ND.NOTE_ID WHERE (TARGET_PARTY_ID = ?) ORDER BY NOTE_DATE_TIME DESC Oracle does not know what TARGET_PARTY_ID is because the column does not exist in any of the tables being queried. Solution #1: OFBiz should Construct the query so Oracle treats the data like a database view. By doing this, you can reference the column aliases: SELECT * FROM (SELECT pn.PARTY_ID as TARGET_PARTY_ID, nd.NOTE_ID AS NOTE_ID, nd.NOTE_NAME AS NOTE_NAME ,nd.NOTE_DATE_TIME AS NOTE_DATE_TIME, nd.NOTE_PARTY AS NOTE_PARTY FROM OFBIZ_NEW.PARTY_NOTE PN, OFBIZ_NEW.NOTE_DATA ND WHERE PN.NOTE_ID = ND.NOTE_ID) WHERE target_party_id = ? ORDER BY NOTE_DATE_TIME DESC Solution #2: Use the real column name ("alias"."columnName") in findByAnd: delegator .findByAnd ( "PartyNoteView" , UtilMisc .toMap ( "pn.partyId" , partyId ) , UtilMisc .toList ( "-noteDateTime" ) ) ----------------------------------------- Michael Irving Keynetx, Inc. - Building Solutions for Success Phone: (215) 310.1934 Mobile: (267)474.3564 Fax: (215) 529-5399 email: [EMAIL PROTECTED] * * * Visit Our Web Site: <http://www.keynetx.net/> http://www.keynetx.net * * * ---------------------------------------------------------------------------- ---- NOTICE: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited.
