oracle 列定义模糊，但为什么？(ORA-00918)

sauutmhj 于 2023-10-16 发布在 Oracle

关注(0)|答案(3)|浏览(178)

我正在创建一个为我们运行SQL查询的应用程序。我已经从一个正在工作的脚本中复制了这个查询，但是当我将它粘贴到SQL developer或vs代码中时，它给了我一个ORA-00918：列定义不明确的错误。我找不到问题出在哪里。希望有人能看到它，并解释出了什么问题。

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
;

oracle

来源：https://stackoverflow.com/questions/77140713/column-ambiguously-defined-but-why-ora-00918

3条答案

按热度按时间

arknldoa1#

如果您这样做：

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

并且table_a和table_b都包含列column_name，那么你将得到错误ORA-00918: column ambiguously defined。
解决方案是在不明确的标识符前面加上适当的表别名（或者，如果不使用别名，则加上表名）：

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

由于您没有告诉use哪些列在哪些表中，因此我们无法给予您查询的具体答案;但是您可以应用上述技术，并在任何名称不明确的列前面加上适当的表别名。（或者更好的做法是，在所有标识符前面加上适当的别名。）
您（可能）需要修复的标识符包括：starttimestamp、bridgeid、requestType、RequestSpecification、requestMessage和faultindication。