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 all records from a table, when passed parameter is "null"

Discussion in 'General' started by sania, Jul 30, 2018.

  1. sania

    sania Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    india
    HI All,

    I have a requirement to fetch all records from a table, when the passed parameter is "NULL" in oracle query.

    Ex-

    Query -select * from table1 where date=p_date and flag=p_flag

    Now while fetching data, if I give p_flag as 'Y'- then I'm getting all records where flag=Y.
    If I give p_flag='N'- then I'm getting all the records when flag=N.
    But there are few records where flag=(null) in the table.
    When I give p_flag as NULL- I'm getting the records where flag = Y and N. But I'm not getting the records where flag =(null)
    But my requirement is- when I give p_flag=NULL, then I want all the records where flag=Y, N, NULL.

    How to achieve this?...Please help asap. Any help will be greatly appreciated.

    Thankyou.
     
    Last edited: Jul 30, 2018
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,639
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    To begin with the code you posted won't work no matter how you try to execute it within SQL*Plus. I doubt it would work with Java or any other external tool, either, which makes this difficult, if not impossible, to solve. You NEED to post actual working code, not some statement that LOOKS like it will run.

    That problem aside, nothing is equal to NULL; the way your 'query' is written will never return rows where flag is NULL.

    Post the actual code you are using if you want anyone here to be able to assist you.
     
  3. OracleMine.com

    OracleMine.com Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    India
    Hi,

    Please try the query in below way:

    select * from table1 where date=p_date and NVL(flag, 'X')=NVL(p_flag, NVL(flag, 'X'));

    The above query will fetch the answers with value Y, N, and NULL.

    Please let me know if this does not work.