CODEWITHSUNDEEP
sqloracle-database
SASPYTHON
SASPYTHON

Reputation: 1621

ORA-00918: column ambiguously How to fix the error

I have been looking my cords and am not sure what I did wrong

This code works no error

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

so I changed to this

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

Then start having this erro ORA-00918: column ambiguously How to fix the error I googled around it says alias, but I added alias on my code. so why this error happening?

Upvotes: 0

Views: 229

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

Fix 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')
      )

Then fix the references to the columns.

I'm a little surprised that Oracle accepts the first version. I guess it prunes the columns before checking for ambiguity.

Upvotes: 2

Related Questions