This discussion is archived
9 Replies Latest reply: Dec 26, 2012 4:59 AM by Frank Kulash RSS

How to display required data from emp table?

940443 Newbie
Currently Being Moderated
Hi every one, this is my first post in this portal. I want display the details of emp table.. for that I am using this SQL statement.

select * from emp where mgr=nvl(:mgr,mgr);

when I give the input as 7698 it is displaying the corresponding records... and also when I won't give any input then it is displaying all the records except the mgr with null values.

1)I want to display all the records when I won't give any input including nulls
2)I want to display all the records who's mgr is null

Is there any way to incorporate to include all these in a single query..
  • 1. Re: How to display required data from emp table?
    Karthick_Arp Guru
    Currently Being Moderated
    937440 wrote:
    Hi every one, this is my first post in this portal. I want display the details of emp table.. for that I am using this SQL statement.

    select * from emp where mgr=nvl(:mgr,mgr);

    when I give the input as 7698 it is displaying the corresponding records... and also when I won't give any input then it is displaying all the records except the mgr with null values.

    1)I want to display all the records when I won't give any input including nulls
    2)I want to display all the records who's mgr is null

    Is there any way to incorporate to include all these in a single query..
    Welcome to the Forum!!!

    Try this
    select * from emp where mgr = :mgr or :mgr is null
  • 2. Re: How to display required data from emp table?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    937440 wrote:
    Hi every one, this is my first post in this portal.
    Welcome to the forum!
    Be sure to read the forum FAQ {message:id=9360002}
    I want display the details of emp table.. for that I am using this SQL statement.

    select * from emp where mgr=nvl(:mgr,mgr);

    when I give the input as 7698 it is displaying the corresponding records... and also when I won't give any input then it is displaying all the records except the mgr with null values.

    1)I want to display all the records when I won't give any input including nulls
    2)I want to display all the records who's mgr is null

    Is there any way to incorporate to include all these in a single query..
    It's a little unclear what you're asking.
    The following query always includes rows where mgr is NULL, and when the bind variable :mgr is NULL, it displays all rows:
    SELECT  *
    FROM     emp
    WHERE     LNNVL (mgr != :mgr)
    ;
    That is, when :mgr = 7698, it displays 6 rows, and when :mgr is NULL it displays 14 rows (assuming you're using the Oracle-supplied scott.emp table).

    The following query includes rows where mgr is NULL only when the bind variable :mgr is NULL, in which case it displays all rows:
    SELECT     *
    FROM     emp
    WHERE     :mgr     = mgr
    OR       :mgr       IS NULL
    ;
    When :mgr = 7698, this displays 5 rows, and when :mgr is NULL it displays 14 rows.

    The following query includes rows where mgr is NULL only when the bind variab;e :mgr is NULL, in which case it displays only the rows where mgr is NULL. That is, it treats NULL as a value:
    SELECT     *
    FROM     emp
    WHERE     DECODE ( mgr
                , :mgr, 'OK'
                )     = 'OK'
    ;
    When :mgr = 7698, this displays 5 rows, and when :mgr is NULL, it displays 1 row.
  • 3. Re: How to display required data from emp table?
    940443 Newbie
    Currently Being Moderated
    Thanks for u r reply..

    I am not getting results with the above query for... if I want to "select only those records who's mgr is null"
  • 4. Re: How to display required data from emp table?
    971895 Journeyer
    Currently Being Moderated
    Try...
    select * from emp where mgr is null
  • 5. Re: How to display required data from emp table?
    940443 Newbie
    Currently Being Moderated
    Thanks Frank.. I got the required
  • 6. Re: How to display required data from emp table?
    981337 Newbie
    Currently Being Moderated
    Hi Please check this query,

    create table emp1 ( id number, mgr number);

    insert into emp1 values (1,'');
    insert into emp1 values (2,1);
    insert into emp1 values (3,1);

    commit;

    select * from emp1;

    SELECT     *
    FROM     emp1
    WHERE     mgr     = nvl(:mgr,mgr) or (:mgr is null)
    ;

    Thanks!
  • 7. Re: How to display required data from emp table?
    940443 Newbie
    Currently Being Moderated
    Hi..

    Can I use CASE instead of DECODE..?

    SELECT     *
    FROM     emp
    WHERE     DECODE ( mgr
         , :mgr, 'OK'
         ) = 'OK';
  • 8. Re: How to display required data from emp table?
    971895 Journeyer
    Currently Being Moderated
    you can use...
  • 9. Re: How to display required data from emp table?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    937440 wrote:
    Hi..

    Can I use CASE instead of DECODE..?
    Sure. You can always use CASE instead of DECODE. You can always use DECODE instead of CASE, also.
    For this job, I don't think CASE makes things any simpler or clearer.
    WHERE     DECODE ( mgr
                , :mgr, 'OK'
                )     = 'OK'
    is equivalent to:
    WHERE     CASE
             WHEN   mgr = :mgr     THEN 'OK'
             WHEN   mgr IS NULL
             AND   :mgr IS NULL  THEN 'OK'
         END                        = 'OK'
    but if you don't want to use DECODE, then I would suggest:
    WHERE   mgr          = :mgr
    OR     (    mgr     IS NULL
         AND :mgr     IS NULL
         )
    or
    WHERE   mgr          = :mgr
    OR     NVL (mgr, :mgr)     IS NULL

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points