title: How to use select statement in CASE WHEN ELSE statement in oracle?
body: <p>I have a table <code>USER</code> with these contents:</p>
  
  <pre><code> USER_ID   User_Name   City   zip   PR_USER_ID
  --------- ----------- ------ ----- ------------
   101       AAA         PPP    123   101
   102       BBB         QQQ    345   101
   103       CCC         RRR    567   NULL
   104       FFF         SSS    789   102
  </code></pre>
  
  <p>I need to add a query inside the procedure for display the <code>User_Name</code>:</p>
  
  <pre><code>CASE WHEN PR_USER_ID IS NULL
       THEN 'NO_PR_USER'   
       ELSE ... get the `User_Name`
                corressponding to USER.USERID = USER.PR_USER_ID
  </code></pre>
  
  <p>Like as I need:</p>
  
  <pre><code>SELECT
    CASE
      WHEN USR.PR_USER_ID IS NULL THEN 'NO PR_USER' 
      ELSE (
        SELECT USR.NAME
        FROM USER USR
        WHERE USR.USER_ID = USR.PR_USR_ID
      ) PRIMARY_USER_NAME
    END 
  </code></pre>
  
  <p>Using this query I am getting the <code>USER_NAME</code>:</p>
  
  <pre><code>SELECT
    PRIMARY_USER.NAME AS PRIMARY_USER_NAME
  FROM USER SECONDARY_USER
  INNER JOIN USER PRIMARY_USER ON PRIMARY_USER.USER_id = SECONDARY_USER.PR_USER_ID; 
  </code></pre>
  
  <p>but I need the Output like this:</p>
  
  <pre><code>  PR_USER_ID   PRIMARY_USER_NAME
   ------------ -------------------
    101          AAA
    101          AAA
    NULL         No_PR_USER
    102          BBB
  </code></pre>
  
  <p>How to get my result in <code>CASE WHEN ELSE</code> statement or any other way?</p>
  
  <p>Please give me an example.</p>