OFBiz › OFBiz - Dev

Errors: findByAnd with Oracle DB

‹ Previous Topic Next Topic ›
 
classic Classic list List threaded Threaded
♦
♦
 9 messages Options
Michael Irving
Reply | Threaded
Open this post in threaded view
♦
♦
|

Errors: findByAnd with Oracle DB

Michael Irving
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: [hidden email]

 

* * * 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.

 

Jacques Le Roux
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Errors: findByAnd with Oracle DB

Jacques Le Roux
Administrator
Michael,

Why not create a Jira issue and patches (2 in you case I  guess ?)

Thanks

Jacques

----- Message d'origine -----
De : "Michael Irving" <[hidden email]>
À : <[hidden email]>
Envoyé : mardi 29 mai 2007 18:27
Objet : 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: [hidden email]
>
>
>
> * * * 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.
>
>
>
>

David E Jones
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Errors: findByAnd with Oracle DB

David E Jones
In reply to this post by Michael Irving

What does your datasource element look like in the entityengine.xml file?

It sounds like your alias-view-columns may be incorrect, just try true instead of false or vice-versa.

-David


Michael Irving wrote:

> 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: [hidden email]
>
>  
>
> * * * 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.
>
>  
>
>
Michael Irving
Reply | Threaded
Open this post in threaded view
♦
♦
|

RE: Errors: findByAnd with Oracle DB

Michael Irving
In reply to this post by Michael Irving
Does anyone have a fix or work around for this problem?

 

  _____  

From: Michael Irving [mailto:[hidden email]]
Sent: Tuesday, May 29, 2007 12:28 PM
To: '[hidden email]'
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: [hidden email]

 

* * * 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.

 

David E Jones
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Errors: findByAnd with Oracle DB

David E Jones

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:[hidden email]]
> Sent: Tuesday, May 29, 2007 12:28 PM
> To: '[hidden email]'
> 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: [hidden email]
>
>  
>
> * * * 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.
>
>  
>
>
Michael Irving
Reply | Threaded
Open this post in threaded view
♦
♦
|

RE: Errors: findByAnd with Oracle DB

Michael Irving
David,

Sorry, I did see a reply from you.

-----Original Message-----
From: David E Jones [mailto:[hidden email]]
Sent: Wednesday, May 30, 2007 11:24 AM
To: [hidden email]
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:[hidden email]]
> Sent: Tuesday, May 29, 2007 12:28 PM
> To: '[hidden email]'
> 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: [hidden email]
>
>  
>
> * * * 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.
>
>  
>
>

Michael Irving
Reply | Threaded
Open this post in threaded view
♦
♦
|

RE: Errors: findByAnd with Oracle DB

Michael Irving
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:[hidden email]]
Sent: Wednesday, May 30, 2007 11:32 AM
To: [hidden email]
Subject: RE: Errors: findByAnd with Oracle DB

David,

Sorry, I did see a reply from you.

-----Original Message-----
From: David E Jones [mailto:[hidden email]]
Sent: Wednesday, May 30, 2007 11:24 AM
To: [hidden email]
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:[hidden email]]
> Sent: Tuesday, May 29, 2007 12:28 PM
> To: '[hidden email]'
> 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: [hidden email]
>
>  
>
> * * * 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.
>
>  
>
>


David E Jones
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Errors: findByAnd with Oracle DB

David E Jones
In reply to this post by David E Jones

Here it is again...

-David


David E Jones wrote:

>
> What does your datasource element look like in the entityengine.xml file?
>
> It sounds like your alias-view-columns may be incorrect, just try true
> instead of false or vice-versa.
>
> -David
>
>
> Michael Irving wrote:
>> 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: [hidden email]
>>
>>  
>>
>> * * * 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.
>>
>>  
>>
>>
>
David E Jones
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Errors: findByAnd with Oracle DB

David E Jones
In reply to this post by Michael Irving

This should be in there now. I made some changes yesterday after responding to your message.

Good luck!

-David


Michael Irving wrote:

> 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:[hidden email]]
> Sent: Wednesday, May 30, 2007 11:32 AM
> To: [hidden email]
> Subject: RE: Errors: findByAnd with Oracle DB
>
> David,
>
> Sorry, I did see a reply from you.
>
> -----Original Message-----
> From: David E Jones [mailto:[hidden email]]
> Sent: Wednesday, May 30, 2007 11:24 AM
> To: [hidden email]
> 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:[hidden email]]
>> Sent: Tuesday, May 29, 2007 12:28 PM
>> To: '[hidden email]'
>> 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: [hidden email]
>>
>>  
>>
>> * * * 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.
>>
>>  
>>
>>
>
>
Free forum by Nabble Edit this page