Ankit Ankit - 4 months ago 12
SQL Question

Use a select within a select statement in Oracle

I am trying to execute the following query:

SELECT pt.prod_desc"Description",
(
CASE
WHEN pt.prod_level='2'
THEN 'Product'
WHEN pt.prod_level='4'
THEN 'Sub-Product'
WHEN pt.prod_level='5'
THEN 'Service'
ELSE 'N/A'
END)"Level", prod_id"CKC ID", isactive"Active", updt_usr_sid"Last Updated By",
updt_ts"LAst Updated Date",
(CASE WHEN pt.prod_level='5' THEN parent_prod_id ELSE null END)"Parent Sub-Product CKC ID",
(CASE WHEN Level='Service' THEN (Select prod_desc from dims_prod_type where prod_id= parent_prod_id) ELSE 'N/A' END)
FROM dims_prod_type pt
ORDER BY prod_desc;


I get the following error:
Error report:


SQL Error: ORA-01788: CONNECT BY clause required in this query block
01788. 00000 - "CONNECT BY clause required in this query block"
*Cause:
*Action:



I modified my query to the following:

SELECT pt.prod_desc"Description",
(
CASE
WHEN pt.prod_level='2'
THEN 'Product'
WHEN pt.prod_level='4'
THEN 'Sub-Product'
WHEN pt.prod_level='5'
THEN 'Service'
ELSE 'N/A'
END)"Level", prod_id"CKC ID", isactive"Active", updt_usr_sid"Last Updated By",
updt_ts"LAst Updated Date",
(CASE WHEN pt.prod_level='5' THEN parent_prod_id ELSE null END)"Parent Sub-Product CKC ID",
(CASE WHEN Level='Service' THEN (Select prod_desc from dims_prod_type where connect by prior prod_id=parent_prod_id) ELSE 'N/A' END)
FROM dims_prod_type pt
ORDER BY prod_desc;


Still getting the same error.

Answer

You've made two mistakes here:

  1. You called a column with level Oracle reserved keyword
  2. You are referring to level column in the same context it was created with the case/when: that column does not exists as a standalone column in that scope and cannot be referred in where/group by clauses, just in order by clauses
Comments