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.