I have an Oracle SQL query that is giving me a "ORA-00918: column ambiguously defined" error on a line that is a comment line

Question

As I said in the title, I am getting the ORA-00918 error but the problem is that it is pointing to a line that is an entire comment line.

In the code below, it is the line written in RED and highlighted in YELLOW. I even deleted a blank line above it and the error still pointed to the comment line. If anyone sees the place where it might be pointing, please let me know. After you have looked at code so many times you are often not really seeing it any more. (This is my first ever post on this site. Please let me know if I need to include any other info. )

Code is below:

WITH SEL_DKTS as
(
  SELECT  DISTINCT   --  count(distinct c.dscr) --DISTINCT
      C.case_id, C.dscr CASE_NO_2, 
      TKL_CD, FNCDKTTXT150(C.case_id, dkt.dkt_id) DKT_TEXT  ,
  CASE  
    WHEN DKT_CD = 'CRMCERTCSO' and FNCDKTTXT150(C.case_id, dkt.dkt_id) like 'PENDING Certification of     
     Record Received from CSOSA'
   THEN SUBSTR(FNCDKTTXT150(C.case_id, dkt.dkt_id), 47, 5) 
   WHEN DKT_CD = 'CRMCERTCSO' and FNCDKTTXT150(C.case_id, dkt.dkt_id) like 'Certification of Record    
     Received from CSOSA%'
   THEN SUBSTR(FNCDKTTXT150(C.case_id, dkt.dkt_id), 39, 5) 
   WHEN DKT_CD = 'CRMCERTPSA' and FNCDKTTXT150(C.case_id, dkt.dkt_id) like 'PENDING Certification of 
     Record Received from PSA'  
   THEN SUBSTR(FNCDKTTXT150(C.case_id, dkt.dkt_id), 47, 3) 
   WHEN DKT_CD = 'CRMCERTCSO' and FNCDKTTXT150(C.case_id, dkt.dkt_id) like 'Certification of Record 
     Received from PSA%'
   THEN SUBSTR(FNCDKTTXT150(C.case_id, dkt.dkt_id), 39, 3)
   WHEN DKT_CD = 'CRMCERTDOC' and FNCDKTTXT150(C.case_id, dkt.dkt_id) like 'PENDING Certification of 
     Record Received from DOC'
   THEN SUBSTR(FNCDKTTXT150(C.case_id, dkt.dkt_id), 47, 3) 
   WHEN DKT_CD = 'CRMCERTCSO' and FNCDKTTXT150(C.case_id, dkt.dkt_id) like 'Certification of Record 
     Received from DOC%'
   THEN SUBSTR(FNCDKTTXT150(C.case_id, dkt.dkt_id), 39, 3)
 ELSE 'ERROR_PEND'
END AS dkt_text_CMMNT  
FROM TKL, REAL_CASE  C,  DKT
 WHERE TKL.case_id = C.case_id and
       TKL.case_id = DKT.case_id and
       DKT.dkt_st_cd is NULL and
  to_char(TKL.entry_dt, 'YYYYMMDD') >= '20180801'  and   
  to_char(TKL.entry_dt, 'YYYYMMDD') <= '20180831'  and   
 (FNCDKTTXT150(C.case_id, dkt.dkt_id) like 'Certification of Record Received from%PENDING' OR
  FNCDKTTXT150(C.case_id, dkt.dkt_id) like ' Pending Certification%' ) AND
  TKL.tkl_cd in ( 'CRMSEAL' )  and
  DKT.dkt_cd like 'CRMCERT%'      )

,SEL_TKL as ( 
  select S1.case_id, S1.CASE_NO_2, TKL.CASE_ID, TKL.SEQ, TKL.TKL_CD, TKL.ENTRY_DT, TKL.COMPLETION_DT, 
         fnctklTxt500(TKL.CASE_ID, TKL.SEQ) TKL_CMNT, S1.DKT_TEXT_CMMNT
   from SEL_DKTS S1, TKL
 where 
    S1.case_id = TKL.case_id and
    TKL.tkl_cd = 'CRMSEAL'                 )


/* ********************************************************************** */     
/* ********************* CONNECT PIECES TOGETHER  *********************** */     
/* ********************************************************************** */  *<----line with error*

SELECT      DISTINCT
   S1.case_id,
   S1.CASE_NO_2,   
   S2.TKL_CD, S2.ENTRY_DT, 
   S1.DKT_TEXT_CMMNT,   
   S1.dkt_text
 FROM SEL_DKTS S1, SEL_TKL S2  
 WHERE S1.case_id = S2.case_id    
 ORDER BY S1.case_no_2` 

Answer 1

Your second CTE:

,SEL_TKL as ( 
  select S1.case_id, S1.CASE_NO_2, TKL.CASE_ID, TKL.SEQ, TKL.TKL_CD, TKL.ENTRY_DT, TKL.COMPLETION_DT, 
         fnctklTxt500(TKL.CASE_ID, TKL.SEQ) TKL_CMNT, S1.DKT_TEXT_CMMNT
   from SEL_DKTS S1, TKL
 where 
    S1.case_id = TKL.case_id and
    TKL.tkl_cd = 'CRMSEAL'                 )

includes both S1.case_id and TKL.CASE_ID in its select list. As you don't have column aliases that will cause the ORA-00918 error. In the main query, when you refer to S2.case_id, which is SEL_TKL.case_id, that could mean either of those values.

In this case your join condition is S1.case_id = TKL.case_id so as you know they are the same you can just remove either of those from the CTE's select list.

---

Just a couple of other observations. Firstly you should really look at using modern join syntax. Secondly converting a column value to text to compare it like:

  to_char(TKL.entry_dt, 'YYYYMMDD') >= '20180801'  and   
  to_char(TKL.entry_dt, 'YYYYMMDD') <= '20180831'  and   

would stop a plain index on entry_date being used. You can compare against a range of dates instead:

  TKL.entry_dt >= date '2018-08-01' and   
  TKL.entry_dt <  date '2018-09-01' and   

Notice that the < date '2018-09-01' will include all times on 2018-08-31, the same as your original.

And thirdly you are repeating a lot of function calls like FNCDKTTXT150(C.case_id, dkt.dkt_id) which may cause them to be evaluated more than once per row; perhaps see if they are, and consider declaring them deterministic if that is appropriate (make sure you understand what that means though), or evaluating them once in a prior CTE. What actually happens is up to the optimiser but that could be something that slows down your code.