Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Users List" <torque-user@db.apache.org>
Received-SPF: pass (hermes.apache.org: local policy)
content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: SV: Column ambiguously defined
Date: Thu, 28 Apr 2005 12:05:37 +0200
Message-ID: <AA6BE599751E774887FE89D10C8B03B304DD237D@oslex1.statnett.no>
Thread-Topic: Column ambiguously defined
Thread-Index: AcVLDRsb80dgsalwTriAhXm/5rJZNQAyyhBg
From: "Bouvet Konsulent" <bouvetkons@statnett.no>
To: "Apache Torque Users List" <torque-user@db.apache.org>

hello again,
thank you for all your help, it has been very useful.

1. Your diagnoisis was right. Using criteria.setLimit(0); made the query =
work fine

2. I tried to do a
ChannelDocumentLatestPeer.doSelectJoinDocument(crit);
This did not work very well, however. The generated SQL was exactly like =
the query which generated a 'ORA-00918: column ambiguously defined' error=
=2E
I am using Oracle 8.1.7, which does not handle joins very well. Keywords =
like 'inner join' were not introduced before version 9. Maybe that has so=
mething to do with it? Does Torque generate SQL based on the _version_ of=
 the database, by using the corresponding jdbc-jar?

3. I ended up doing
List docs =3D DocumentPeer.doSelectVillageRecords(crit);
transfer docs into objects
building a criteria for ChannelDocumentLatest
List l =3D ChannelDocumentLatestPeer.doSelectVillageRecords(cdlCriteria);=

transfer l into object 'latest'
doc.setOriginatingChannel(latest.getOriginatingChannelId());

so I ended up with one more SQL-query to get my foreign key to add to the=
 Document-object, but I guess that is ok.

once more: thank you for your help!

cheers,
pj

-----Opprinnelig melding-----
Fra: Thomas Fischer [mailto:fischer@seitenbau.net]
Sendt: 27. april 2005 11:40
Til: Apache Torque Users List
Emne: SV: Column ambiguously defined


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 <=3D 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 doSelectJoinXX=
X.
Is there a reason why you do not use them ? Could you please give it a tr=
y
?

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 joi=
n
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 =3D 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:1=
1:

> 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=3DCriteria:: document.IS_DELETED<=3D>document.IS_DELETED=3D0:
> channel_document_latest.CHANNEL_ID<=3D>channel_document_latest.
> CHANNEL_ID=3D26944:
> 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=3D0 AND
> channel_document_latest.CHANNEL_ID=3D26944 AND document.
> ID=3Dchannel_document_latest.DOCUMENT_ID
>
> 2005-04-27 10:43:18,235 DEBUG no.acme.om.util.GenericSearcher - crit
> 42=3DCriteria:: document.IS_DELETED<=3D>document.IS_DELETED=3D0:
> channel_document_latest.CHANNEL_ID<=3D>channel_document_latest.
> CHANNEL_ID=3D26944:
> 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=3D0 AND
> channel_document_latest.CHANNEL_ID=3D26944 AND document.
> ID=3Dchannel_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=3D"+crit);
>         if (channelid =3D=3D 0) { //Dont allow this yet
>             return new ArrayList();
>         }
>         if (crit =3D=3D null) {
>             crit =3D new Criteria();
>         }
>         //crit =3D getChannelDocumentCriteria(channelid, crit);
>         crit.add(ChannelDocumentLatestPeer.CHANNEL_ID, new
> NumberKey(channelid));
>         crit.addJoin(DocumentPeer.ID,
ChannelDocumentLatestPeer.DOCUMENT_ID);
>         if (metadata !=3D null && !"".equals(metadata)) {
>             crit.addJoin(DocumentPeer.ID,
DocumentMetadataPeer.DOCUMENT_ID);
>             crit.addJoin(DocumentMetadataPeer.METADATA_ID,
MetadataPeer.ID);
>             StringBuffer buf =3D 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=3D"+crit);
>         ChannelDocumentLatestPeer.addSelectColumns(crit);
>         logger.debug("crit 42=3D"+crit);
>         crit.addDescendingOrderByColumn(DocumentPeer.CHANGED);
>         crit.addDescendingOrderByColumn(DocumentPeer.ID);
>
>         //Join two tables
>         List documents =3D null;
>         try{
>             List experiment =3D DocumentPeer.doSelectVillageRecords(cri=
t);
>             documents =3D new ArrayList(experiment.size());
>             // populate the object(s)
>             for (int i =3D 0; i < experiment.size(); i++)
>             {
>                 Record row =3D (Record) experiment.get(i);
>                 no.acme.om.Document doc =3D DocumentPeer.
> row2Object(row, 1, DocumentPeer.getOMClass());
>                 ChannelDocumentLatest latest =3D
> 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 Offs=
et
> 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=3D1 AND
> > channel_document_latest.CHANNEL_ID=3D26944 AND document.
> > ID=3Dchannel_document_latest.DOCUMENT_ID ORDER BY document.CHANGED
> > DESC, document.ID DESC ) A  ) B WHERE  B.TORQUE$ROWNUM <=3D 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 name=
s
in
> > the Peer classes (which have the tablename prepended to the columnnam=
e,
> so
> > that the column names are unique within the schema).
> >
> > You can look at the Torque debug log to get more information about th=
e
> > 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 chos=
en
> 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=
=2E
> > > 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 pr=
ivileged. If you have received this transmission in error please notify t=
he 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 in=
formation could be intercepted, corrupted, lost, destroyed, arrive late o=
r incomplete. If verification is required please request a hard copy vers=
ion. This e-mail message has been virus checked by the virus programs use=
d by Statnett SF.


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