David,
You were right. The "default" settings for Oracle did not have a
alias-view-columns setting at all. Using alias-view-columns="false" did the
trick.
Since I'm not a "committer", can you or someone else add
alias-view-columns="false" to both: localoracle and localoracledd?
Thanks for your help.
-Mike
-----Original Message-----
From: Michael Irving [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 30, 2007 11:32 AM
To: dev@ofbiz.apache.org
Subject: RE: Errors: findByAnd with Oracle DB
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: dev@ofbiz.apache.org
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: '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.
