Solved

Oracle Error Column Ambigously defined

Posted on 2014-11-12
3
269 Views
Last Modified: 2014-11-22
Why am I getting an error on this row

 WHERE PLAN_ID IN ('207')

"Column Ambigously Defined"



_________________________________________
SELECT
(Sum(case when STAT_C IN ('A') AND PCNT > 1 and DOL = 0 then 1 else 0 end)+Sum(case when STAT_C IN('A') AND PCNT < 1 and DOL > 0 then 1 else 0 end))/Sum(case when STAT_C IN('A') then 1 else 0 end) as PART_RATE
FROM(
SELECT SSN_N, FROM dbo.V_FDPAT

  LEFT JOIN(
      SELECT PLAN_ID, SSN_ID
         FROM dbo.V_FDPDF
           Group by PLAN_ID, SSN_ID
      ) C ON A.SSN_ID=C.SSN_ID AND  A.PLAN_ID=C.PLAN_ID

 WHERE PLAN_ID IN ('207')
and Row_Eff_D >= '31-Jul-14'
)
0
Comment
Question by:leezac
3 Comments
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40438728
Because you need to specify from which table you are referencing the column PLAN_ID is it A or C i.e.


SELECT
(Sum(case when STAT_C IN ('A') AND PCNT > 1 and DOL = 0 then 1 else 0 end)+Sum(case when STAT_C IN('A') AND PCNT < 1 and DOL > 0 then 1 else 0 end))/Sum(case when STAT_C IN('A') then 1 else 0 end) as PART_RATE
FROM( 
SELECT SSN_N, FROM dbo.V_FDPAT

  LEFT JOIN(
      SELECT PLAN_ID, SSN_ID
         FROM dbo.V_FDPDF
           Group by PLAN_ID, SSN_ID
      ) C ON A.SSN_ID=C.SSN_ID AND  A.PLAN_ID=C.PLAN_ID

 WHERE A.PLAN_ID IN ('207')
and Row_Eff_D >= '31-Jul-14'
)

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40438761
>>and Row_Eff_D >= '31-Jul-14'

Not for points since Haris has answered the question.
I just wanted to encourage you to start doing explicit data type conversions and note rely on Oracle to 'guess'.

and Row_Eff_D >= to_date('31-Jul-14','DD-Mon-YY')
0
 

Author Closing Comment

by:leezac
ID: 40459506
Thank you!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question