title: Use a select within a select statement in Oracle
body: <p>I am trying to execute the following query:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>I get the following error:
  Error report:</p>
  
  <blockquote>
  <pre><code>SQL Error: ORA-01788: CONNECT BY clause required in this query block
  01788. 00000 -  "CONNECT BY clause required in this query block"
  *Cause:    
  *Action:
  </code></pre>
  </blockquote>
  
  <p>I modified my query to the following:</p>
  
  <pre><code> 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;
  </code></pre>
  
  <p>Still getting the same error.</p>