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

Question

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?

Littlefoot · Accepted Answer · 2023-01-27 08:29:01Z

1

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.

answered Jan 27, 2023 at 8:29

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hayley Over a year ago

It works well. thank you i learned it!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related