1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Fetch record for the default value, if record doesn't exist..

Discussion in 'SQL PL/SQL' started by Vicky, Jul 17, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi Frndz..

    I wanna fetch record for the value which has been given, if record for the given value, if doesn't exist, then i wanna fetch record for the default value..

    For eg:

    I wanna fetch record from employees table for the given dept_id., it no records exists in the table for the given dept_id, i wanna fetch records for the dept_id='General'...

    I've tried something, which works only when the dept doesn't exists..


    Code (SQL):



       SELECT * FROM EMPLOYEES;


        EMP_ID EMP_NAME                 SALARY JOB_ID     MANAGER_ID DEPT_ID
    ---------- -------------------- ---------- ---------- ---------- ----------
           200 Jennifer                   4400 AD_ASST    101        IT      
           202 Pat                        6000 MK_REP     201        ECE      
           119 Karen                      2500 PU_CLERK   114        IT      
           203 Susan                      6500 HR_REP     101        GENERAL  
           197 Kevin                      3000 SH_CLERK   124        ECE      
           107 Diana                      4200 IT_PROG    103        GENERAL  
           204 Hermann                   10000 PR_REP     101        IT      
           179 Charles                    6200 SA_REP     149        GENERAL  
           102 Lex                       17000 AD_VP      100        ECE      
           113 Luis                       6900 FI_ACCOUNT 108        MECH    
           206 William                    8300 AC_ACCOUNT 205        IT      
           178 Kimberely                  7000 SA_REP     149        MECH    

    12 ROWS selected

     


    Code (SQL):


    SELECT * FROM EMPLOYEES
    WHERE dept_id LIKE decode (dept_id,'CS','CS','GENERAL');



        EMP_ID EMP_NAME                 SALARY JOB_ID     MANAGER_ID DEPT_ID
    ---------- -------------------- ---------- ---------- ---------- ----------
           203 Susan                      6500 HR_REP     101        GENERAL  
           107 Diana                      4200 IT_PROG    103        GENERAL  
           179 Charles                    6200 SA_REP     149        GENERAL

     

    If exists, it is fetching the records for the given dept_id , and also for the default dept_id ..


    Code (SQL):


    SELECT * FROM EMPLOYEES
    WHERE dept_id LIKE decode (dept_id,'IT','IT','GENERAL');


        EMP_ID EMP_NAME                 SALARY JOB_ID     MANAGER_ID DEPT_ID
    ---------- -------------------- ---------- ---------- ---------- ----------
           200 Jennifer                   4400 AD_ASST    101        IT      
           119 Karen                      2500 PU_CLERK   114        IT      
           203 Susan                      6500 HR_REP     101        GENERAL  
           107 Diana                      4200 IT_PROG    103        GENERAL  
           204 Hermann                   10000 PR_REP     101        IT      
           179 Charles                    6200 SA_REP     149        GENERAL  
           206 William                    8300 AC_ACCOUNT 205        IT      

    7 ROWS selected

     
    Pls correct me if I'm wrong some where or suggest me any best way to do the same..
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    OK -- First of all -- DECODE doesn't work like that. The statement: "DECODE(dept_id,'CS','CS','GENERAL')" can be read as "If the value of dept_id is CS, then return CS, otherwise return GENERAL". DECODE will return one of those two values. Since you are comparing the result with the DEPT_ID column, any time the DEPT_ID value is CS, DECODE will return CS, the condition will evaluate to TRUE and the row will be returned. Any time the DEPT_ID value is not CS, the DECODE will return GENERAL, and the row will be returned if it has a DEPT_ID value of GENERAL.

    Nothing in the DECODE pays attention to anything in rows *other* than the one it is comparing, so it is completely oblivious to whether the table contains zero rows with DEPT_ID=CS or a million.

    Doing what you are asking will require a subquery or a WITH clause. The result of the subquery/WITH clause will be used as a factor in the main query. The following example uses a WITH clause. The first time, it's run for department_id 1000 (which has no employees) and the second time it is run with department_id 100, which does have employees.

    Code (Text):

    WITH cs AS
      (SELECT NVL(MAX(department_id), 60) department_id
       FROM   hr.employees
       WHERE  department_id = 1000)
    SELECT employee_id, first_name, last_name, emp.department_id
    FROM   hr.employees emp
           INNER JOIN cs
           ON emp.department_id = cs.department_id;
         
    EMPLOYEE_ID FIRST_NAME      LAST_NAME     DEPARTMENT_ID
    ----------- --------------- ------------- -------------
            103 Alexander       Hunold                   60
            104 Bruce           Ernst                    60
            105 David           Austin                   60
            106 Valli           Pataballa                60
            107 Diana           Lorentz                  60

    WITH cs AS
      (SELECT NVL(MAX(department_id), 60) department_id
       FROM   hr.employees
       WHERE  department_id = 100)
    SELECT employee_id, first_name, last_name, emp.department_id
    FROM   hr.employees emp
           INNER JOIN cs
           ON emp.department_id = cs.department_id;

    EMPLOYEE_ID FIRST_NAME      LAST_NAME     DEPARTMENT_ID
    ----------- --------------- ------------- -------------
            108 Nancy           Greenberg               100
            109 Daniel          Faviet                  100
            110 John            Chen                    100
            111 Ismael          Sciarra                 100
            112 Jose Manuel     Urman                   100
            113 Luis            Popp                    100
     
    Vicky likes this.
  3. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    What is the use of function MAX over here? is it only to return a single value in case department_id is more than 1? ca we also use MIN instead??
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Yes to both questions. Since the query is filtering for a single department, the aggregate function will return either that department ID (if one or more exists) or NULL. If it returns NULL, then the NVL will convert the value returned to the 'default' department ID. I could also have used AVG for my query since the column was numeric -- although that would have failed for the OP since his query used a character-based department name.
     
    mukulverma2408 likes this.