db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <fisc...@seitenbau.net>
Subject SV: Column ambiguously defined
Date Wed, 27 Apr 2005 09:39:58 GMT





Hi,

the problem seems to be the limit/offset in the criteria in conjunction
with the adding by hand of columns (because of the join).
Somewhere above, you seem to apply criteria.setLimit(10), otherwise the
part WHERE  B.TORQUE$ROWNUM <= 10 would not appear in the SQL.

Can you please do the following to see if this diagnosis is correct:
- use a criteria without limit/offset (then it should work) (this is not
what you want, but chekcs whether the diagnosis is right)
- I am not sure whether this can solve your problem, but there are
generated methods to join on foreign keys. They are called doSelectJoinXXX.
Is there a reason why you do not use them ? Could you please give it a try
?

If my diagnosis is correct and the doSelectJoinXXX does not work, the
solution which comes to my mind is to fill only one datatype from the join
and get the related data by hand. You can use the following PSEUDOcode to
do this:

construct criteria exactly as you did (including the join);
List documents DocumentPeer.doSelect(criteria);
// now only the documents have been loaded
// load the channelDocumentLatest documents
foreach (document in Documents) {
   Criteria cdlCriteria = new Criteria();
   fill cdl to get the wanted channelDocumentLatest;
   document.getChannelDocumentLatest(channelDocumentLatestCriteria);
}

Please tell us whether the diagnosis was correct and how you solved the
problem.

      Thomas

"Bouvet Konsulent" <bouvetkons@statnett.no> schrieb am 27.04.2005 11:02:11:

> hello again,
> sorry for spamming you with my problems, but I just don't seem to
> find a solution. I am using Torque version 3.1. The method
> findDocuments() is the method that adds the SQL that gives an error.
> The following debugs comes from the method below:
>
> 2005-04-27 10:43:18,235 DEBUG no.acme.om.util.GenericSearcher - crit
> 41=Criteria:: document.IS_DELETED<=>document.IS_DELETED=0:
> channel_document_latest.CHANNEL_ID<=>channel_document_latest.
> CHANNEL_ID=26944:
> Current Query SQL (may not be complete or applicable): SELECT
> DISTINCT document.ID, document.URL, document.FILENAME, document.
> FILEPREFIX, document.OBJECTTYPE_ID, document.IS_DELETED, document.
> TITLE, document.IS_NEW, document.CREATED, document.CHANGED FROM
> document, channel_document_latest WHERE document.IS_DELETED=0 AND
> channel_document_latest.CHANNEL_ID=26944 AND document.
> ID=channel_document_latest.DOCUMENT_ID
>
> 2005-04-27 10:43:18,235 DEBUG no.acme.om.util.GenericSearcher - crit
> 42=Criteria:: document.IS_DELETED<=>document.IS_DELETED=0:
> channel_document_latest.CHANNEL_ID<=>channel_document_latest.
> CHANNEL_ID=26944:
> Current Query SQL (may not be complete or applicable): SELECT
> DISTINCT document.ID, document.URL, document.FILENAME, document.
> FILEPREFIX, document.OBJECTTYPE_ID, document.IS_DELETED, document.
> TITLE, document.IS_NEW, document.CREATED, document.CHANGED,
> channel_document_latest.DOCUMENT_ID, channel_document_latest.
> CHANNEL_ID, channel_document_latest.ORIGINATING_CHANNEL_ID,
> channel_document_latest.CHANGED FROM document,
> channel_document_latest WHERE document.IS_DELETED=0 AND
> channel_document_latest.CHANNEL_ID=26944 AND document.
> ID=channel_document_latest.DOCUMENT_ID
>
> 2005-04-27 10:43:18,251 WARN  no.acme.om.util.GenericSearcher -
> Problems finding Documents: org.apache.torque.TorqueException:
> ORA-00918: column ambiguously defined
>
>
>
>
> public List findDocuments(int channelid, Criteria crit, String
> metadata, String text) throws Exception {
>         logger.debug("findDocuments, crit="+crit);
>         if (channelid == 0) { //Dont allow this yet
>             return new ArrayList();
>         }
>         if (crit == null) {
>             crit = new Criteria();
>         }
>         //crit = getChannelDocumentCriteria(channelid, crit);
>         crit.add(ChannelDocumentLatestPeer.CHANNEL_ID, new
> NumberKey(channelid));
>         crit.addJoin(DocumentPeer.ID,
ChannelDocumentLatestPeer.DOCUMENT_ID);
>         if (metadata != null && !"".equals(metadata)) {
>             crit.addJoin(DocumentPeer.ID,
DocumentMetadataPeer.DOCUMENT_ID);
>             crit.addJoin(DocumentMetadataPeer.METADATA_ID,
MetadataPeer.ID);
>             StringBuffer buf = new StringBuffer();
>             if (!metadata.startsWith("*"))
>                 buf.append("*");
>             buf.append(metadata.trim());
>             if (!metadata.endsWith("*"))
>                 buf.append("*");
>             crit.add(MetadataPeer.VALUE, (Object) buf.toString(),
> Criteria.LIKE);
>             crit.setIgnoreCase(true);
>         }
>         crit.setDistinct();
>         DocumentPeer.addSelectColumns(crit);
>         logger.debug("crit 41="+crit);
>         ChannelDocumentLatestPeer.addSelectColumns(crit);
>         logger.debug("crit 42="+crit);
>         crit.addDescendingOrderByColumn(DocumentPeer.CHANGED);
>         crit.addDescendingOrderByColumn(DocumentPeer.ID);
>
>         //Join two tables
>         List documents = null;
>         try{
>             List experiment = DocumentPeer.doSelectVillageRecords(crit);
>             documents = new ArrayList(experiment.size());
>             // populate the object(s)
>             for (int i = 0; i < experiment.size(); i++)
>             {
>                 Record row = (Record) experiment.get(i);
>                 no.acme.om.Document doc = DocumentPeer.
> row2Object(row, 1, DocumentPeer.getOMClass());
>                 ChannelDocumentLatest latest =
> ChannelDocumentLatestPeer.row2Object(row, DocumentPeer.numColumns +
> 1, ChannelDocumentLatestPeer.getOMClass());
>
doc.setOriginatingChannel(latest.getOriginatingChannelId());
>                 documents.add(doc);
>             }
>         }
>         catch(TorqueException e){
>             logger.warn("Problems finding Documents: "+e);
>         }
>
>
>
> -----Opprinnelig melding-----
> Fra: Thomas Fischer [mailto:fischer@seitenbau.net]
> Sendt: 27. april 2005 09:38
> Til: Apache Torque Users List
> Emne: RE: Column ambiguously defined
>
>
>
>
>
>
> Hi,
>
> The problem seems to be the limit and offset processing. Limit and Offset
> work differently in postgres and oracle.
> Which Torque version do you use ?
> How do you construct the criteria to get this query ?
>
>      Thomas
>
> "Bouvet Konsulent" <bouvetkons@statnett.no> schrieb am 26.04.2005
15:24:04:
>
> > hello again,
> > I got the following output from Torque:
> >
> > DEBUG http-8080-Processor24 org.apache.torque.util.BasePeer - SELECT
> > B.* FROM ( SELECT A.*, rownum AS TORQUE$ROWNUM FROM ( SELECT
> > DISTINCT document.ID, document.URL, document.FILENAME, document.
> > FILEPREFIX, document.OBJECTTYPE_ID, document.IS_DELETED, document.
> > TITLE, document.IS_NEW, document.CREATED, document.CHANGED,
> > channel_document_latest.DOCUMENT_ID, channel_document_latest.
> > CHANNEL_ID, channel_document_latest.ORIGINATING_CHANNEL_ID,
> > channel_document_latest.CHANGED FROM document,
> > channel_document_latest WHERE document.IS_DELETED=1 AND
> > channel_document_latest.CHANNEL_ID=26944 AND document.
> > ID=channel_document_latest.DOCUMENT_ID ORDER BY document.CHANGED
> > DESC, document.ID DESC ) A  ) B WHERE  B.TORQUE$ROWNUM <= 10
> >
> > this gives the following error:
> > org.apache.torque.TorqueException: ORA-00918: column ambiguously
defined
> >
> > The SQL above is a bit complex, but the following SQL would give the
> > same result:
> > select A.* from (select document.CHANGED, channel_document_latest.
> > CHANGED from document, channel_document_latest) A;
> >
> > I have a column called CHANGED in both tables document and
> > channel_document_latest, which Oracle 8.1.7 does not like.
> > The following SQL would be valid for Oracle:
> > select A.* from (select document.CHANGED, channel_document_latest.
> > CHANGED CHANGED_1 from document, channel_document_latest) A;
> >
> > The code was previously running fine on Postgres, which may indicate
> > that Postgres inserts aliases automatically?
> >
> > As BasePeer is the class that generates this SQL, I do not see how I
> > could solve this without changing the columnnames of my database
> > (which would be quite a challenge)
> >
> > any suggestions?
> >
> > cheers,
> > Per Jorgen
> >
> > -----Opprinnelig melding-----
> > Fra: Thomas Fischer [mailto:fischer@seitenbau.net]
> > Sendt: 26. april 2005 12:16
> > Til: Apache Torque Users List
> > Emne: RE: Column ambiguously defined
> >
> >
> >
> >
> >
> >
> > Hi,
> >
> > Torque does not handle ambiguously defined columns by itself. Usually,
> this
> > does not appear if  you use the constants defined for the column names
in
> > the Peer classes (which have the tablename prepended to the columnname,
> so
> > that the column names are unique within the schema).
> >
> > You can look at the Torque debug log to get more information about the
> > error. For this, you have to configure a logger supported by the
> > org.apache.commons.logging framework. Perhaps it can also be found in
the
> > JBoss logs. If it is a select that causes the error and you have chosen
> the
> > loglevel to be "debug", the query should be logged in the debug log.
> >
> >    Thomas
> >
> > "Bouvet Konsulent" <bouvetkons@statnett.no> schrieb am 26.04.2005
> 11:45:08:
> >
> > > hello list,
> > > has anyone seen the following error when using Torque 3.1, Oracle 8.
> > > 1.7 and jBoss 4.0.1 (Tomcat 5)?
> > >
> > > INFO  [STDOUT] org.apache.torque.TorqueException: ORA-00918: column
> > > ambiguously defined
> > >
> > > I understand the ORA-error, but I just don't know where this could
> > > come from within Torque... no stack-trace, nothing, just this
> > > message. Eventually, the application crashes with a javax.servlet.
> > > jsp.JspException, but that stack trace does not give any helpful
> > > information either. I thought Torque should be able to handle
> > > ambiguous column-names?
> > >
> > > Anny suggestions would be appreciated...
> > >
> > > cheers,
> > > Per Jorgen
> > >
> > >
> > >
>
>
>
> Statnett SF, Tel. head office +47 22527000, Enterprise No. NO 962
> 986 633 VAT, www.statnett.no, firmapost@statnett.no
> _________________________________________________________________
>
> This e-mail and any attached files are confidential and may be
> legally privileged. If you have received this transmission in error
> please notify the sender by reply e-mail immediately and then delete
> the e-mail.
> E-mail transmission cannot be guaranteed to be secure or error free
> as information could be intercepted, corrupted, lost, destroyed,
> arrive late or incomplete. If verification is required please
> request a hard copy version. This e-mail message has been virus
> checked by the virus programs used by Statnett SF.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message