9 Replies Latest reply: Dec 26, 2012 6:59 AM by Frank Kulash RSS

    How to display required data from emp table?

    940443
      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
          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
            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
              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
                Try...
                select * from emp where mgr is null
                • 5. Re: How to display required data from emp table?
                  940443
                  Thanks Frank.. I got the required
                  • 6. Re: How to display required data from emp table?
                    981337
                    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
                      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
                        you can use...
                        • 9. Re: How to display required data from emp table?
                          Frank Kulash
                          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