Re: SQL Error

Posted by David E Jones on
URL: http://ofbiz.116.s1.nabble.com/SQL-Error-tp145761p145765.html


It looks like the entityengine.xml file OOTB does not specify a alias-
view-columns, so it would default to true.

Are you saying that adding alias-view-columns=false does not fix this  
problem?

Are you sure it was set and the server restarted? The SQL listed  
below is the type of SQL that would be generated if it was set to  
true. Namely the FIXED_ASSET_ID in the WHERE clause is not prefixed  
with "FAM." which it would be if that was set to false.

-David


On Apr 18, 2007, at 3:49 PM, Anil Patel wrote:

> I am getting following error. Same code works fine on a snapshot of  
> code
> that was put in production about  month back. I am not sure if this  
> Problem
> also has to do with what Adrian reported. I am still getting this  
> error even
> after David's recent fix.
>
> This is happening on MS SQL Server.
>
> org.ofbiz.entity.GenericDataSourceException: SQL Exception while  
> executing
> the following:SELECT FAM.
> FIXED_ASSET_ID AS FIXED_ASSET_ID, FAM.SCHEDULE_WORK_EFFORT_ID AS
> SCHEDULE_WORK_EFFORT_ID, FAM.INTERV
> AL_QUANTITY AS INTERVAL_QUANTITY, WE.WORK_EFFORT_ID AS WORK_EFFORT_ID,
> WE.ACTUAL_COMPLETION_DATE AS
> ACTUAL_COMPLETION_DATE FROM dbo.FIXED_ASSET_MAINT FAM INNER JOIN
> dbo.WORK_EFFORT WE ON FAM.SCHEDULE_
> WORK_EFFORT_ID = WE.WORK_EFFORT_ID WHERE (FIXED_ASSET_ID = ?) ORDER BY
> ACTUAL_COMPLETION_DATE DESC (
> Ambiguous column name 'FIXED_ASSET_ID'.)
>        at org.ofbiz.entity.jdbc.SQLProcessor.executeQuery
> (SQLProcessor.java
> :399)
>
> Any tips
>
> Regards
> Anil
>
>
>
> On 4/18/07, Adrian Crum <[hidden email]> wrote:
>>
>> David,
>>
>> That fixed it. Thank you very much for the quick reply!
>>
>>
>> David E. Jones wrote:
>>
>> >
>> > This is related to the MySQL issue that was reported recently.
>> >
>> > There was a bug in the entity engine causing certain settings on  
>> the
>> > datasource element in the entityengine.xml file to be ignored. The
>> > setting in the advantage config, and various others, that was being
>> > ignored is the "alias-view-columns" attribute. That was set to  
>> true  and
>> > it looks like Advantage (like many other databases) does NOT  
>> support
>> > aliased view columns, ie aliases for fields in joins.
>> >
>> > To fix this just change alias-view-columns=true to alias-view-
>> > columns=false. This is now changed in SVN.
>> >
>> > -David
>> >
>> >
>> > On Apr 18, 2007, at 3:10 PM, Adrian Crum wrote:
>> >
>> >> I'm trying to update my local copy of OFBiz to the latest SVN.  
>> Last
>> >> version used was back in 2005. Now I'm getting SQL exceptions:
>> >>
>> >> 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
>> >> PARTY_NOTE PN INNER JOIN NOTE_DATA ND ON PN.NOTE_ID =  ND.NOTE_ID
>> >> WHERE (TARGET_PARTY_ID = ?) ORDER BY NOTE_DATE_TIME DESC  
>> ([Extended
>> >> Systems][Advantage SQL Engine]Column not found:  
>> TARGET_PARTY_ID --
>> >> Location of error in the SQL statement is: 259)
>> >>
>> >> Any idea what changed or what could be wrong?
>> >>
>> >
>>