David, Sorry, I did see a reply from you.
-----Original Message----- From: David E Jones [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 30, 2007 11:24 AM To: [email protected] Subject: Re: Errors: findByAnd with Oracle DB Did you see my reply to this? Did that not work? -David Michael Irving wrote: > 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: '[email protected]' > 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. > > > >
