Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: Please Help how to use fetch null rows using decode

    In a table the rows to be fetch where if she given input as null then all rows to be output otherwise the appropriate row will be the output.

    for above requirement i write below query

    select * from emp
    where
    (comm= decode('&comm',NULL,comm,'&comm') or comm is null)
    and (sal = decode('&sal',null,sal,'&sal') or sal is null)
    /


    from above query i'm getting correct output.

    but i want to handle " or sal is null " in decode function it self



    Thanks in advance
    Last edited by siri4DB; 04-30-09 at 23:20.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but i want to handle " or sal is null " in decode function it self
    Why?

    >from above query i'm getting correct output.
    Quit while you have what you want.

    >but i want to handle " or sal is null " in decode function it self
    Why do you think you can do as you desire?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2009
    Posts
    4
    I submit same query to my manager.


    but she tell dont use every time "or sal is null", remove that and handle it in decode it self.

    In a table the rows to be fetch where if she given input as null then all rows to be output otherwise the appropriate row will be the output.

    could u please give me solution for the above requirement.


    Please help me.
    Last edited by siri4DB; 04-30-09 at 23:00.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    why even use decode

    Code:
    select * from emp 
    where  comm= nvl('&comm',comm)
    and sal = nvl('&sal',sal)
    /
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Apr 2009
    Posts
    4
    Thanks for reply.


    But for above query i'm not getting null rows.


    i mean i'm not getting complete rows when i entered sal,comm as null.


    but i want null rows also.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but i want null rows also.
    Including the "null rows" requires OR SAL IS NULL; because NULL <> NULL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Apr 2009
    Posts
    4
    Thanks for reply

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •