column ambiguously defined, but why? (ORA-00918)

I’m creating an application that runs SQL queries for us. I’ve copied this query from a script that is working, but when I paste it in SQL developer or vs code it gives me a ORA-00918: column ambiguously defined error. I can’t find where the issue lies. Hopefully someone can see it and explain what is going wrong.

    select to_char(STARTTIMESTAMP, 'yyyymmdd hh24:mi:ss DY') as "DATE",
       bridgeid,
       requestType,
       RequestSpecification,
       bfa.faultdescription,
       case
         when requestMessage is not null then
          dbms_lob.substr(substr(bMes.requestMessage,
                                 INSTR(bMes.requestMessage, 'SourceSystem', 1) +
                                 length('SourceSystem') + 1,
                                 (INSTR(bMes.requestMessage,
                                        'SourceSystem',
                                        1,
                                        2) - INSTR(bMes.requestMessage,
                                                    'SourceSystem',
                                                    1) -
                                 length('SourceSystem') - 3)))
       end as sourceSystem,
       case
         when requestMessage is not null then
          dbms_lob.substr(substr(bMes.requestMessage,
                                 INSTR(bMes.requestMessage, 'EventId', 1) +
                                 length('EventId') + 1,
                                 (INSTR(bMes.requestMessage, 'EventId', 1, 2) -
                                 INSTR(bMes.requestMessage, 'EventId', 1) -
                                 length('EventId') - 3)))
       end as EventId,
       bint.MessageId,
       bint.relatesto,
       systimestamp
  from baseintegrationheaders bint
  left join baseMessages bMes
    on bMes.relatesto = bint.relatesto
  left join basefaults bfa
    on bfa.relatesto = bint.relatesto
where 1 = 1
   and STARTTIMESTAMP Between
       (trunc(systimestamp, 'hh') +
       floor(to_char(systimestamp, 'mi') / 15) * 15 / 1440) - 15 / 1440 And
       trunc(systimestamp, 'hh') +
       floor(to_char(systimestamp, 'mi') / 15) * 15 / 1440
   and bint.technicaldomain = 'WB'
   and bint.bridgeid in ('AST_GPSBatch_Reply-bridge',
                          'AST_GPSOnline_Reply-bridge',
                          'AST_GPS_Request-bridge')
   and requestType not in ( 'WorkerRunning'
                          , 'BridgeEnd'
                          , 'BridgeStart'
                          , 'WorkerEnd'
                          , 'WorkerMQEnd'
                          , 'WorkerMQStart'
                          , 'WorkerProducerEnd'
                          , 'WorkerProducerStart'
                          , 'WorkerReaderEnd'
                          , 'WorkerReaderStart'
                          , 'WorkerStart'
                          , 'WorkerStop'
                          )
   and ((requestType = 'BridgeRequest' and faultindication = 'true') or
       (requestType <> 'BridgeRequest'))
order by bint.MessageId, 1
;

>Solution :

If you do:

SELECT column_name
FROM   table_a a
       INNER JOIN table_b b
       ON (a.something = b.something)

and both table_a and table_b contain the column column_name then you will get the error ORA-00918: column ambiguously defined.

The solution is to prefix the ambiguous identifier with the appropriate table alias (or, if you are not using aliases, table name):

SELECT b.column_name
FROM   table_a a
       INNER JOIN table_b b
       ON (a.something = b.something)

Since you do not tell use which columns are in which tables we cannot give you a specific answer for your query; but you can apply the above technique and prefix any ambiguously named columns with the appropriate table aliases. (Or better, prefix all identifiers with the appropriate aliases.)

The identifiers you (may) need to fix include: starttimestamp, bridgeid, requestType, RequestSpecification, requestMessage and faultindication.