how to select specific columns from three different tables in Oracle SQL-CodePudding

I am trying to select values from three different tables. When I select all columns it works well, but if I select specific column, the SQL Error [42000]: JDBC-8027:Column name is ambiguous. appear.

this is the query that selected all that works well

SELECT 
*
FROM (SELECT x.*, B.*,C.* , COUNT(*) OVER (PARTITION BY x.POLICY_NO) policy_no_count 
FROM YIP.YOUTH_POLICY x
 LEFT JOIN
 YIP.YOUTH_POLICY_AREA B
 ON x.POLICY_NO = B.POLICY_NO
 LEFT JOIN
 YIP.YOUTH_SMALL_CATEGORY C
 ON B.SMALL_CATEGORY_SID = C.SMALL_CATEGORY_SID
ORDER BY x.POLICY_NO);

and this is the error query

SELECT DISTINCT
x.POLICY_NO,
x.POLICY_TITLE,
policy_no_count ,
B.SMALL_CATEGORY_SID,
C.SMALL_CATEGORY_TITLE 
FROM (SELECT x.*, B.*,C.* , COUNT(*) OVER (PARTITION BY x.POLICY_NO) policy_no_count 
        FROM YIP.YOUTH_POLICY x
        LEFT JOIN
         YIP.YOUTH_POLICY_AREA B
        ON x.POLICY_NO = B.POLICY_NO
        LEFT JOIN
         YIP.YOUTH_SMALL_CATEGORY C
        ON B.SMALL_CATEGORY_SID = C.SMALL_CATEGORY_SID
        ORDER BY x.POLICY_NO);

I am trying to select if A.POLICY_NO values duplicate rows more than 18, want to change C.SMALL_CATEGORY_TITLE values to "ZZ" and also want to cahge B.SMALL_CATEGORY_SID values to null. that is why make 2 select in query like this

SELECT DISTINCT
x.POLICY_NO,
CASE WHEN (policy_no_count > 17) THEN 'ZZ' ELSE C.SMALL_CATEGORY_TITLE END AS C.SMALL_CATEGORY_TITLE,
CASE WHEN (policy_no_count > 17) THEN NULL ELSE B.SMALL_CATEGORY_SID END AS B.SMALL_CATEGORY_SID,
x.POLICY_TITLE
FROM (SELECT x.*, B.*,C.* , COUNT(*) OVER (PARTITION BY x.POLICY_NO) policy_no_count 
        FROM YIP.YOUTH_POLICY x
        LEFT JOIN
         YIP.YOUTH_POLICY_AREA B
        ON x.POLICY_NO = B.POLICY_NO
        LEFT JOIN
         YIP.YOUTH_SMALL_CATEGORY C
        ON B.SMALL_CATEGORY_SID = C.SMALL_CATEGORY_SID
        ORDER BY x.POLICY_NO);

If i use that query, I got SQL Error [42000]: JDBC-8006:Missing FROM keyword. ¶at line 3, column 80 of null error..

I know I should solve it step by step. Is there any way to select specific columns?

CodePudding user response：

That's most probably because of SELECT x.*, B.*,C.* - avoid asterisks - explicitly name all columns you need, and then pay attention to possible duplicate column names; if you have them, use column aliases.

For example, if that select (which is in a subquery) evaluates to

select x.id, x.name, b.id, b.name

then outer query doesn't know which id you want as two columns are named id (and also two names), so you'd have to

select x.id   as x_id,
       x.name as x_name,
       b.id   as b_id,
       b.name as b_name
from ...

and - in outer query - select not just id, but e.g. x_id.