Menu
I have this table on oracle db:
Can i do nested select query that change the PARENT column to the name associated with it? like this:
I have tried:
SELECT PC.ID
,(
SELECT PC.NAME from PRODUCTS_CATEGORIES PC where PC.PARENT = PC.ID
)
,PC.NAME
FROM PRODUCTS_CATEGORIES PC
and got this error:
Error Message: ORA-01427: single-row sub query returns more than one row
222
First of all, you can put a nested SELECT within the WHERE clause with comparison operators or the IN , NOT IN , ANY , or ALL operators. The second group of operators are used when your subquery returns a list of values (rather than a single value, as in the previous example):
A subquery in the WHERE clause of a SELECT statement is also called a nested subquery. You can nest up to 255 levels of subqueries in the a nested subquery. A subquery can contain another subquery. Oracle Database imposes no limit on the number of subquery levels in the FROM clause of the top-level query.
Oracle Single Row SubqueryThe subquery must return a single row; a subquery written without a Oracle WHERE Clause (hence usually returns more than one row) will generate an error. The subquery must return a single column; specifying more than one column in the subquery's SELECT clause will result in an error.
In the Oracle RDBMS, it is possible to use a multi-row subquery in the select clause as long as the (sub-)output is encapsulated as a collection.
You can do this
SELECT PC.ID,
(
SELECT DISTINCT PC2.NAME from PRODUCTS_CATEGORIES PC2
where PC.PARENT = PC2.ID AND PC2.PARENT = 0
),
PC.NAME
FROM PRODUCTS_CATEGORIES PC
OR
SELECT PC.ID,NVL(PC2.NAME,PC.PARENT) PC2_NAME,PC.NAME
FROM PRODUCTS_CATEGORIES PC
LEFT OUTER JOIN
(SELECT DISTINCT ID, NAME
from PRODUCTS_CATEGORIES WHERE PARENT = 0) PC2 ON PC.PARENT = PC2.ID
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
