代码之家 › 专栏 › 技术社区 › SASPYTHON

ORA-00918:列不明确如何修复错误

oracle sql

SASPYTHON · 技术社区 · 9 年前

这段代码没有错误

WITH comc AS (
            SELECT ncc1.allocation_code,ncc1.note_type,ncc1.note_text,ncc1.description
            FROM  notes ncc1 
            WHERE ncc1.note_type = 'CC'
           ),
  coms AS  (
            SELECT ncc2.allocation_code,ncc2.note_type,ncc2.note_text,ncc2.description
            FROM  notes ncc2 
            WHERE ncc2.note_type = 'CS'
           ),
get_note AS ( 
           SELECT n1.allocation_code alloc_code, cc3.note_type, cc3.note_text, cs4.note_type, cs4.note_text,row_number() OVER (PARTITION BY n1.allocation_code ORDER BY n1.allocation_code) ranki
           FROM notes n1
           LEFT JOIN comc cc3   ON cc3.allocation_code = n1.allocation_code
           LEFT JOIN coms cs4  ON  cs4.allocation_code = n1.allocation_code 
           WHERE n1.note_type IN ('CC','CS')
           )
           SELECT gt.alloc_code 
           FROM   get_note     gt

所以我改成了这个

WITH comc AS (
            SELECT ncc1.allocation_code,ncc1.note_type,ncc1.note_text,ncc1.description
            FROM  notes ncc1 
            WHERE ncc1.note_type = 'CC'
           ),
  coms AS  (
            SELECT ncc2.allocation_code,ncc2.note_type,ncc2.note_text,ncc2.description
            FROM  notes ncc2 
            WHERE ncc2.note_type = 'CS'
           ),
get_note AS ( 
           SELECT n1.allocation_code alloc_code, cc3.note_type, cc3.note_text, cs4.note_type, cs4.note_text,row_number() OVER (PARTITION BY n1.allocation_code ORDER BY n1.allocation_code) ranki
           FROM notes n1
           LEFT JOIN comc cc3   ON cc3.allocation_code = n1.allocation_code
           LEFT JOIN coms cs4  ON  cs4.allocation_code = n1.allocation_code 
           WHERE n1.note_type IN ('CC','CS')
           )
           SELECT gt.alloc_code 
           FROM   allocation allo 
 LEFT JOIN  get_note     gt                 ON gt.alloc_code = allo.allocation_code

我在谷歌上搜索了一下,上面写着别名,但我在代码中添加了别名。

1 回复 | 直到 9 年前

Gordon Linoff 9 年前

修理 get_note

  get_note AS ( 
       SELECT n1.allocation_code alloc_code,
              cc3.note_type as note_type_3, cc3.note_text as note_text_3,
-----------------------------------------^
              cs4.note_type as note_type_4, cs4.note_text as note_text_4,
              row_number() OVER (PARTITION BY n1.allocation_code ORDER BY n1.allocation_code) ranki LEFT JOIN
       FROM notes n1
            comc cc3
            ON cc3.allocation_code = n1.allocation_code LEFT JOIN
            coms cs4
            ON  cs4.allocation_code = n1.allocation_code 
       WHERE n1.note_type IN ('CC','CS')
      )

我有点惊讶Oracle接受了第一个版本。我想它在检查歧义之前会对列进行修剪。