db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bouvet Konsulent" <bouvetk...@statnett.no>
Subject SV: Column ambiguously defined
Date Wed, 27 Apr 2005 09:02:11 GMT
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


Mime
View raw message