0

I am a new bie to the world of oracle but i have the below oracle query as shown below that is throwing an exception , and the issue is that in the outermost query I did not wish to write the column names as there are count of 78 columns in the table , now please advise how to overcome from the exception for the below oracle query or let me know the alternatives to this

SELECT * FROM ( WITH 
  BASE AS ( 
  SELECT TEMP.*, COUNT(*) OVER () AS TOTAL_RECORDS 
  FROM ( 
  SELECT AFT.*,
      PA.*,
      P.*
    FROM AFT_DATA AFT,
      CONTACT_TOKEN CT, 
      PRODUCT_ATTRIBUTE PA,
      PERSON P
    WHERE AFT.person_id          = 1
    AND AFT.TO_CONTACT_TOKEN_ID  = CT.CONTACT_TOKEN_ID
    AND AFT.PRODUCT_ATTRIBUTE_ID = PA.PRODUCT_ATTRIBUTE_ID
    AND AFT.PERSON_ID            = P.PERSON_ID
    AND AFT.CREATED_ON          >= CAST ('04/04/2018' AS DATE)
    AND AFT.CREATED_ON          <= CAST ('05/05/2018' AS DATE)) TEMP ) SELECT 
    ROUND((ROWNUM - MOD(ROWNUM-1, 1 )) / 1) +1 PAGE_NUMBER,  B.*  FROM base B) WHERE PAGE_NUMBER = 1 ;

The exception stack trace for the above query is 

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"
user1726942
  • 407
  • 2
  • 4
  • 10
  • Does the exception give you a line number or position in the sql for the offending column? – Sam M Oct 13 '18 at 03:11
  • 1
    My guess is that you have a column name duplicated in the `SELECT AFT.*, PA.*, P.*` fragment - so you will have to enumerate all columns in all those tables, using "AS" to rename the one that is duplicated... – moilejter Oct 13 '18 at 03:14
  • @moilejter well there are count of 80 columns do i need to write all – user1726942 Oct 13 '18 at 03:29
  • @SamM yes on this line number ELECT TEMP.*, COUNT(*) OVER () AS TOTAL_RECORDS – user1726942 Oct 13 '18 at 03:35
  • The comment by @moilejter is the answer to your question. You have duplicate column names, Oracle can't resolve them, so you have to use aliases. – Sam M Oct 13 '18 at 04:25
  • Possible duplicate of [ORA-00918: column ambiguously defined i am getting this error](https://stackoverflow.com/questions/32815808/ora-00918-column-ambiguously-defined-i-am-getting-this-error) – Sam M Oct 13 '18 at 04:25
  • You could find the column name that is duplicate, find the table that has the fewest columns and includes the duplicate, and only enumerate all columns for that one table, using "AS" when needed to rename that column in the query result... – moilejter Oct 13 '18 at 19:58

0 Answers0