9 Replies Latest reply: Nov 18, 2011 1:41 AM by user117992 RSS

    Getting EQP-80407 & ORA-00918 error in SES query

    user117992
      Hi,

      We are using SES to implement Third party search and created a new data source of type "Database" to crawl the external db. when we start the scheduler to crawl the db, getting the following error:

      EQP-80407: Exception while processing document: ORA-00918: column ambiguously defined


      Here is the query used while creating the datasource:

      SELECT 'SWERowId0='||SR.ROW_ID||''AS URL,SR.ROW_ID AS KEY, 'en' AS LANG,SR.LAST_UPD AS LASTMODIFIEDDATE,'SR#: ' ||SR.SR_NUM || CHR(13) || 'Asset Business Segment: '||O.NAME || CHR(13) || 'Installed At Customer Short Name: ' ||OX.ATTRIB_07 || CHR(13) ||'Dispatch To Last Name: ' ||CON.LAST_NAME || CHR(13) || 'Version: ' ||SRX.ATTRIB_07 || CHR(13) || 'Asset Description: ' ||ASS.DESC_TEXT || CHR(13) ||'Short Description: ' ||SR.X_GEHC_IITS_SHORT_DESCRIPTION || CHR(13) || 'Status: ' ||SR.SR_STAT_ID || CHR(13)|| 'Responsible Queue: ' ||USR.LOGIN || CHR(13) || 'Created Date: ' ||SR.X_GEHC_IITS_CREATED || CHR(13) || 'Resolution: '||SR.X_RESOLUTION || CHR(13) || 'SPR ID: ' ||SRX.ATTRIB_41|| CHR(13) || 'Type: ' ||SR.SR_CAT_TYPE_CD || CHR(13) || 'SubType: ' ||SR.SR_SUBTYPE_CD || CHR(13) || 'Description:
      '||SR.DESC_TEXT || CHR(13) || 'Actual Problem: '|| SR.X_ACTUAL_PROBLEM || CHR(13) || 'Owner Last Name: '|| emp.LAST_NAME || CHR(13) || CHR(13) || 'Action: ' ||ACT.SUBTYPE_CD || CHR(13) || 'Type: ' ||ACT.TODO_CD || CHR(13) || 'Status: ' ||ACT.EVT_STAT_CD || CHR(13) || 'Comments: '||ACT.COMMENTS_LONG || CHR(13) ||CHR(13) ||CHR(13) || 'SR#: ' ||SR.SR_NUM || CHR(13) || 'Customer Name: ' ||ORG.NAME || CHR(13) || 'Created Date/Time: '||ACT.X_GEHC_IITS_CREATED || CHR(13) || 'Last Updated Date/Time: '||ACT.LAST_UPD || CHR(13)|| 'Asset Business Segment: '||O.NAME || CHR(13) || 'Asset Description: ' ||ASS.DESC_TEXT || CHR(13) || 'Version: ' ||SRX.ATTRIB_07 || CHR(13) || 'Responsible Queue: ' ||USR.LOGIN ||CHR(13) || 'Customer Short Name: '||OX.ATTRIB_07 || CHR(13) || 'SR Owner First Name: ' ||emp.FST_NAME || CHR(13) || 'SR Owner Last Name: '||emp.LAST_NAME || CHR(13) || 'Customer Contact: ' ||CON3.LAST_NAME|| CHR(13) || 'Owner Last Name: ' ||CON4.LAST_NAME as CONTENT,

      'SR #:' ||SR.SR_NUM || ' - ' || 'Activity#: ' ||ACT.ACTIVITY_UID as title,

      SR.SR_NUM as SR#,
      O.NAME as ASSETBUSINESSSEGMENT,
      OX.ATTRIB_07 as INSTALLEDATCUSTOMERSHORTNAME,
      CON.LAST_NAME as DISPATCHTOLASTNAME,
      SRX.ATTRIB_07 as VERSION,
      SR.SR_STAT_ID as SRSTATUS,
      USR.LOGIN as RESPONSIBLEQUEUE,
      SR.X_GEHC_IITS_CREATED as CREATEDDATE,
      ASS.DESC_TEXT as ASSETDESCRIPTION,
      SR.X_GEHC_IITS_SHORT_DESCRIPTION as SHORTDESCRIPTION,
      emp.LAST_NAME as OWNERLASTNAME,
      CON2.LAST_NAME as CONTACTLASTNAME,
      SR.LAST_UPD as LASTEDITEDUPDATEDDATE,
      ACT.EVT_STAT_CD as ACTIVITYSTATUS,
      ACT.X_GEHC_IITS_CREATED as CREATEDDATETIME,
      ACT.LAST_UPD as LASTUPDATEDDATETIME,
      CON3.LAST_NAME as CUSTOMERCONTACT,
      CON4.LAST_NAME as OWNERLASTNAME

      FROM SIEBEL.S_SRV_REQ SR,SIEBEL.S_ASSET ASS,SIEBEL.S_ORG_EXT O,SIEBEL.S_ORG_EXT_X OX,SIEBEL.S_CONTACT CON,SIEBEL.S_CONTACT emp,SIEBEL.S_SRV_REQ_X SRX,SIEBEL.S_CONTACT CON2,SIEBEL.S_USER USR,SIEBEL.S_EVT_ACT ACT,SIEBEL.S_CONTACT CON3,SIEBEL.S_CONTACT CON4 ,SIEBEL.S_ORG_EXT ORG,SIEBEL.S_ACT_CONTACT CONACT WHERE SR.ASSET_ID = ASS.ROW_ID(+) AND ASS.BU_ID = O.PAR_ROW_ID(+) AND SR.CST_OU_ID=OX.PAR_ROW_ID(+) AND SR.X_DISPATCH_ID=CON.PAR_ROW_ID(+) AND SR.OWNER_EMP_ID=EMP.PAR_ROW_ID(+) AND SR.ROW_ID=SRX.PAR_ROW_ID(+) AND SR.CST_CON_ID=CON2.PAR_ROW_ID(+) AND SR.X_GEHC_IITS_QUEUE_OWNER = USR.ROW_ID(+) AND SR.ROW_ID = ACT .SRA_SR_ID(+) AND ACT.OWNER_PER_ID = CON4.PAR_ROW_ID(+) AND SR.CST_OU_ID = ORG.PAR_ROW_ID(+) AND ACT.ROW_ID = CONACT.ACTIVITY_ID(+) AND CONACT.CON_ID = CON3.ROW_ID(+) and SR.LAST_UPD >= to_date('10/14/2011','MM/DD/YYYY')


      Here is the snippet of the crawler log:

      06:05:21:307 INFO     filter_0          Queueing URL:http://iits-web-dev-01.am.health.ge.com:10091/emedical_enu/start.swe?SWECmd=GotoView&SWEView=Defects+Detail&SWERF=1&SWEBU=1&SWEApplet0=Product+Defect+Detail+Form+Applet&SWERowId0=1-2PDOEF, key:1-2PDOEF, lastmod:2011-10-13T06:25:09
      06:05:21:314 DEBUG     filter_1     JdbcDocumentQueueManager          batch dequeue(ms): 6 (5678859)
      06:05:21:314 INFO     filter_1          Dequeued http://iits-web-dev-01.am.health.ge.com:10091/emedical_enu/start.swe?SWECmd=GotoView&SWEView=Defects+Detail&SWERF=1&SWEBU=1&SWEApplet0=Product+Defect+Detail+Form+Applet&SWERowId0=1-2PDOEF
      06:05:21:314 DEBUG     filter_1          Access url is http://foo?key=1-2PDOEF&lastmod=2011-10-13T06:25:09 KEYVAL is 1-2PDOEF
      06:05:21:314 DEBUG     filter_0     JdbcDocumentQueueManager          existing Query(ms):0
      06:05:21:320 FATAL     filter_1          EQP-80407: Exception while processing document: ORA-00918: column ambiguously defined

      06:05:21:320 FATAL     filter_1     ORA-00918: column ambiguously defined
      java.sql.SQLException     oracle.jdbc.driver.DatabaseError:throwSqlException:125     oracle.jdbc.driver.T4CTTIoer:processError:316     oracle.jdbc.driver.T4CTTIoer:processError:282     oracle.jdbc.driver.T4C8Oall:receive:639     oracle.jdbc.driver.T4CPreparedStatement:doOall8:185     oracle.jdbc.driver.T4CPreparedStatement:execute_for_describe:503     oracle.jdbc.driver.OracleStatement:execute_maybe_describe:965     oracle.jdbc.driver.T4CPreparedStatement:execute_maybe_describe:535     oracle.jdbc.driver.OracleStatement:doExecuteWithTimeout:1051     oracle.jdbc.driver.OraclePreparedStatement:executeInternal:2984     oracle.jdbc.driver.OraclePreparedStatement:executeQuery:3026     oracle.search.plugin.appsjdbc.AppsJdbcPlugin:processDocuments:1084     oracle.search.plugin.appsjdbc.AppsJdbcPlugin:crawl:1456     oracle.search.crawler.CrawlingThread:run:1578

      We are using LAST_NAME column at diffrenet places in the query from S_CONTACT table using different joins. when I run the same query in SQL, not getting any error as we are using different join aliases.This is happening only when I use the same query to crawl the data in SES datasource.

      Any help on this would be highly appreciated.
        • 1. Re: Getting EQP-80407 & ORA-00918 error in SES query
          Roger Ford-Oracle
          I can't see any problem with this.

          SES will use your query by doing something like:
          SELECT * FROM ( 
            < your query > 
          ) WHERE KEY=<value>
          You could try that and see if you get the same "column ambiguously defined" error. Normally it's caused by more than one column in the SELECT list having the same column name or alias, but I can't see that in your query.

          I take it that the *** is your edit, and not part of the actual query?
          • 2. Re: Getting EQP-80407 & ORA-00918 error in SES query
            user117992
            Thanks for the immediate reply Roger. I have tried the format which you have suggested to use, but still getting the same error.

            CON.LAST_NAME
            emp.LAST_NAME
            CON2.LAST_NAME
            CON3.LAST_NAME
            CON4.LAST_NAME We are fetching these from same table(S_CONTACT), but using different join aliases.

            If I remove the other LAST_NAME instances(emp.LAST_NAME, CON2.LAST_NAME, CON3.LAST_NAME and CON4.LAST_NAME) from the query and run the query in SES, it's working fine.

            Yes, *** is my edit in the query, not the actual query.

            Please advice on this.
            • 3. Re: Getting EQP-80407 & ORA-00918 error in SES query
              Roger Ford-Oracle
              Sorry, I wasn't clear. I wanted you to run that query in SQL*Plus - that is, your query surrounded by SELECT * FROM ( ) WHERE KEY=value.

              I can't see anything wrong with the LAST_NAME usage. Since they're in a concatenation, they won't get individual aliases and it should be fine.

              You might want to turn on crawler debug, then SES will list the query it thinks it's dealing with in the log file.

              To turn on crawler debug:

              In file: $ORACLE_HOME/search/data/config/crawler.dat
              Change:
              SYSTEM_PROPERTIES -Doracle.search.logLevel=4 ...
              to
              SYSTEM_PROPERTIES -Doracle.search.logLevel=2 ...
              • 4. Re: Getting EQP-80407 & ORA-00918 error in SES query
                user117992
                Hi Roger,

                Query format: SELECT * FROM (< your query >) WHERE KEY=<value>

                Can you please tell me what could be the value for "KEY=<value>"

                I have executed the query in SQL*Plus,as per the format as follows by passing sample row id of one record as KEY, but getting the Column ambiguity error.

                SELECT * FROM (SELECT 'SWERowId0='||SR.ROW_ID||''AS URL,SR.ROW_ID AS KEY, 'en' AS LANG,SR.LAST_UPD AS LASTMODIFIEDDATE,'SR#: ' ||SR.SR_NUM || CHR(13) || 'Asset Business Segment: '||O.NAME || CHR(13) || 'Installed At Customer Short Name: ' ||OX.ATTRIB_07 || CHR(13) || 'Dispatch To Last Name: ' ||CON.LAST_NAME || CHR(13) || 'Version: ' ||SRX.ATTRIB_07 || CHR(13) || 'Asset Description: ' ||ASS.DESC_TEXT || CHR(13) || 'Short Description: ' ||SR.X_GEHC_IITS_SHORT_DESCRIPTION || CHR(13) || 'Status: ' ||SR.SR_STAT_ID || CHR(13) || 'Responsible Queue: ' ||USR.LOGIN || CHR(13) || 'Created Date: ' ||SR.X_GEHC_IITS_CREATED || CHR(13) || 'Resolution: '||SR.X_RESOLUTION || CHR(13) || 'SPR ID: ' ||SRX.ATTRIB_41|| CHR(13) || 'Type: ' ||SR.SR_CAT_TYPE_CD || CHR(13) || 'SubType: ' ||SR.SR_SUBTYPE_CD || CHR(13) || 'Description: '||SR.DESC_TEXT || CHR(13) || 'Actual Problem: '|| SR.X_ACTUAL_PROBLEM || CHR(13) || 'Owner Last Name: '|| emp.LAST_NAME || CHR(13) || CHR(13) || 'Action: ' ||ACT.SUBTYPE_CD || CHR(13) || 'Type: ' ||ACT.TODO_CD || CHR(13) || 'Status: ' ||ACT.EVT_STAT_CD || CHR(13) || 'Comments: '||ACT.COMMENTS_LONG || CHR(13) ||CHR(13) ||CHR(13) || 'SR#: ' ||SR.SR_NUM || CHR(13) || 'Customer Name: ' ||ORG.NAME || CHR(13) || 'Created Date/Time: '||ACT.X_GEHC_IITS_CREATED || CHR(13) || 'Last Updated Date/Time: '||ACT.LAST_UPD || CHR(13) || 'Asset Business Segment: '||O.NAME || CHR(13) || 'Asset Description: ' ||ASS.DESC_TEXT || CHR(13) || 'Version: ' ||SRX.ATTRIB_07 || CHR(13) || 'Responsible Queue: ' ||USR.LOGIN || CHR(13) || 'Customer Short Name: '||OX.ATTRIB_07 || CHR(13) || 'SR Owner First Name: ' ||emp.FST_NAME || CHR(13) || 'SR Owner Last Name: ' ||emp.LAST_NAME || CHR(13) || 'Customer Contact: ' ||CON3.LAST_NAME|| CHR(13) || 'Owner Last Name: ' ||CON4.LAST_NAME as CONTENT,'SR #:' ||SR.SR_NUM || ' - ' || 'Activity#: ' ||ACT.ACTIVITY_UID as title,SR.SR_NUM as SR#,O.NAME as ASSETBUSINESSSEGMENT,OX.ATTRIB_07 as INSTALLEDATCUSTOMERSHORTNAME,CON.LAST_NAME as DISPATCHTOLASTNAME,SRX.ATTRIB_07 as VERSION,SR.SR_STAT_ID as SRSTATUS,USR.LOGIN as RESPONSIBLEQUEUE,SR.X_GEHC_IITS_CREATED as CREATEDDATE,ASS.DESC_TEXT as ASSETDESCRIPTION,SR.X_GEHC_IITS_SHORT_DESCRIPTION as SHORTDESCRIPTION,emp.LAST_NAME as OWNERLASTNAME,CON2.LAST_NAME as CONTACTLASTNAME,SR.LAST_UPD as LASTEDITEDUPDATEDDATE,ACT.EVT_STAT_CD as ACTIVITYSTATUS,ACT.X_GEHC_IITS_CREATED as CREATEDDATETIME,ACT.LAST_UPD as LASTUPDATEDDATETIME,CON3.LAST_NAME as CUSTOMERCONTACT,CON4.LAST_NAME as OWNERLASTNAME FROM SIEBEL.S_SRV_REQ SR,SIEBEL.S_ASSET ASS,SIEBEL.S_ORG_EXT O,SIEBEL.S_ORG_EXT_X OX,SIEBEL.S_CONTACT CON,SIEBEL.S_CONTACT emp,SIEBEL.S_SRV_REQ_X SRX,SIEBEL.S_CONTACT CON2,SIEBEL.S_USER USR,SIEBEL.S_EVT_ACT ACT,SIEBEL.S_CONTACT CON3,SIEBEL.S_CONTACT CON4 ,SIEBEL.S_ORG_EXT ORG,SIEBEL.S_ACT_CONTACT CONACT WHERE SR.ASSET_ID = ASS.ROW_ID(+) AND ASS.BU_ID = O.PAR_ROW_ID(+) AND SR.CST_OU_ID=OX.PAR_ROW_ID(+) AND SR.X_DISPATCH_ID=CON.PAR_ROW_ID(+) AND SR.OWNER_EMP_ID=EMP.PAR_ROW_ID(+) AND SR.ROW_ID=SRX.PAR_ROW_ID(+) AND SR.CST_CON_ID=CON2.PAR_ROW_ID(+) AND SR.X_GEHC_IITS_QUEUE_OWNER = USR.ROW_ID(+) AND SR.ROW_ID = ACT .SRA_SR_ID(+) AND ACT.OWNER_PER_ID = CON4.PAR_ROW_ID(+) AND SR.CST_OU_ID = ORG.PAR_ROW_ID(+) AND ACT.ROW_ID = CONACT.ACTIVITY_ID(+) AND CONACT.CON_ID = CON3.ROW_ID(+)) WHERE KEY='1-1MCO39'

                Please help me.

                Thanks
                Sandeep
                • 5. Re: Getting EQP-80407 & ORA-00918 error in SES query
                  user117992
                  Can anyone help me on this..?
                  • 6. Re: Getting EQP-80407 & ORA-00918 error in SES query
                    Roger Ford-Oracle
                    The <value> is any value of KEY.
                    SES starts by fetching all the KEY values, then it calls "SELECT * FROM (your query) WHERE KEY=<value>" for each value of KEY. That gives it all the data for a single row / document.

                    If you're getting the same ambiguous column error is SQL*Plus, then it's not specifically an SES problem.

                    I suggest you try to reproduce it with a script which creates all the tables necessary, and then runs this query. That way, other people can try the query and see if they can find a way around the problem. You might also be able to post it on a more general SQL forum.

                    I could try to guess your column definitions etc and attempt to create the script myself, but it's probably a lot easier for you to do so. If you're not able to do that, then a DESCRIBE of each table involved would help.
                    • 7. Re: Getting EQP-80407 & ORA-00918 error in SES query
                      user117992
                      Thanks alot Roger. It's really helped us to solve our issue.
                      • 8. Re: Getting EQP-80407 & ORA-00918 error in SES query
                        Roger Ford-Oracle
                        Great. Can you share the answer? What did you need to change to get it to work?
                        • 9. Re: Getting EQP-80407 & ORA-00918 error in SES query
                          user117992
                          Hi Roger,

                          There was some mistake in my query.
                          emp.LAST_NAME as OWNERLASTNAME,
                          CON4.LAST_NAME as OWNERLASTNAME
                          As LAST_NAME was fetching as a same alias column, we were getting column ambiguity error. now we have changed those names and is working fine.

                          Thanks
                          Sandeep