CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10goracle-sqldeveloper
Ankit
Ankit

Reputation: 129

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.

Upvotes: 0

Views: 1684

Answers (1)

Vincenzo Maggio
Vincenzo Maggio

Reputation: 3869

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

Upvotes: 2

Related Questions