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!

Need Help in Sql Query

Discussion in 'SQL PL/SQL' started by jagadekara, Nov 4, 2013.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I have two Tables like below.

    create table tab1 (empno number,ename varchar2(30));

    create table tab2 (empno number,status varchar2(30));

    SELECT * FROM tab1;
    Empno Ename
    10 Jagan
    20 Kiran
    30 Ravi

    SELECT * FROM tab2;
    Empno Status
    10 Active
    20 Inactive

    My requirement is where ever status is null there we need to display like below.

    There is No Status for This Employee

    so my final output should be like this.

    Empno Ename Status
    10 Jagan Active
    20 Kiran Inactive
    30 Ravi There is No Status for This Employee
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Jagadekara,

    Try with the below queries

    Code (SQL):
    SELECT t1.empno,t1.ename,NVL(t2.STATUS,'There is No Status for This Employee')
    FROM    tab1 t1
           ,tab2 t2
    WHERE  t1.empno = t2.empno(+)
    OR
    Code (SQL):

    SELECT t1.empno,t1.ename,DECODE(t2.STATUS,NULL,'There is No Status for This Employee',t2.STATUS)
    FROM    tab1 t1
           ,tab2 t2
    WHERE  t1.empno = t2.empno(+)
     


    Regards
    Sambasiva Reddy.K
     
    jagadekara likes this.
  3. sambhaji

    sambhaji Active Member

    Messages:
    62
    Likes Received:
    2
    Trophy Points:
    160
    Hi,

    Please try below query.


    (select tab1.empno,tab1.ename,DECODe(TAB2.status,NULL,'No Status for This Employee ',TAB2.STATUS) Status
    from tab1,
    tab2
    where TAB1.EMPNO=tab2.empno(+))


    Regards
    Sambhaji
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The queries supplied will work but use older Oracle functionality; CASE and the ANSI join syntax may be a better solution for this:

    Code (SQL):
    SQL> CREATE TABLE tab1 (empno NUMBER,ename varchar2(30));
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE tab2 (empno NUMBER,STATUS varchar2(30));
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO tab1
      3  VALUES(10,'Jagan')
      4  INTO tab1
      5  VALUES(20,'Kiran')
      6  INTO tab1
      7  VALUES(30,'Ravi')
      8  INTO tab2
      9  VALUES(10,'Active')
     10  INTO tab2
     11  VALUES(20,'Inactive')
     12  SELECT * FROM dual;
     
    5 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT t1.empno, t1.ename, CASE WHEN t2.STATUS IS NULL THEN 'There Is No Status For This Employee' ELSE t2.STATUS END STATUS
      2  FROM tab1 t1 LEFT OUTER JOIN tab2 t2 ON t2.empno = t1.empno
      3  ORDER BY 1;
     
         EMPNO ENAME                          STATUS
    ---------- ------------------------------ ------------------------------------
            10 Jagan                          Active
            20 Kiran                          Inactive
            30 Ravi                           There IS No STATUS FOR This Employee
     
    SQL>
    SQL> INSERT ALL
      2  INTO tab1
      3  VALUES(40,'Jagaz')
      4  INTO tab1
      5  VALUES(50,'Kiraz')
      6  INTO tab1
      7  VALUES(60,'Raviz')
      8  INTO tab2
      9  VALUES(40,'Active')
     10  INTO tab2
     11  VALUES(60,'Inactive')
     12  SELECT * FROM dual;
     
    5 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT t1.empno, t1.ename, CASE WHEN t2.STATUS IS NULL THEN 'There Is No Status For This Employee' ELSE t2.STATUS END STATUS
      2  FROM tab1 t1 LEFT OUTER JOIN tab2 t2 ON t2.empno = t1.empno
      3  ORDER BY 1;
     
         EMPNO ENAME                          STATUS
    ---------- ------------------------------ ------------------------------------
            10 Jagan                          Active
            20 Kiran                          Inactive
            30 Ravi                           There IS No STATUS FOR This Employee
            40 Jagaz                          Active
            50 Kiraz                          There IS No STATUS FOR This Employee
            60 Raviz                          Inactive
     
    6 ROWS selected.
     
    SQL>