This discussion is archived
4 Replies Latest reply: Dec 6, 2012 7:58 AM by ascheffer RSS

query not returning rows (nvl)

Cleopatra Newbie
Currently Being Moderated
Hi all, I was wondering if anyone could help
I have a query :

select a, b, c, d, e from table_t
where a=nvl(:para, a)
and b=nvl(:parb,b)
and c=nvl(:parc,c)

the problem is is one the rows contain a null, then that row is not returned. I would like to return all rows along with the null values.
for example:

row 1 has b= null then row 1 is not displayed but only the rest of the rows where b is not null are displayed


DB version 11.1.0.6.0 g

All help will be appreciated.

Kind regards,
Cleopatra
  • 1. Re: query not returning rows (nvl)
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Celopatra,

    Depending on your requirements:
    ...
    WHERE   LNNVL (a != :para)
    AND     LNNVL (b != :parb)
    AND     LNNVL (c != :parc)
     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data. Make sure the data shows special situations you care about, for example, if a is NULL, but :para is not NULL.
    Explain, using specific examples, how you get those results from that data.
    In problems like this, it often helps to know if there are impossible values for any of the columns. For example, if a is a DATE, it could be helpful to know if all dates stored in the table are after the year 1000, or, if b is a NUMBER, that it is always in the range -100 to +100.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 2. Re: query not returning rows (nvl)
    Cleopatra Newbie
    Currently Being Moderated
    Hi Frank ,
    thanks for the reply

    I get all the rows displayed now, but the filters do not seem to work for para, parb and parc.
    Any ideas how I could get that?

    Because now I just get all the rows

    Cleo
  • 3. Re: query not returning rows (nvl)
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Cleo,
    Cleopatra wrote:
    Hi Frank ,
    thanks for the reply

    I get all the rows displayed now, but the filters do not seem to work for para, parb and parc.
    Any ideas how I could get that?
    Before moving on to other ideas, let's try the ideas I already mentioned:
    Frank Kulash wrote:
    ... post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data. Make sure the data shows special situations you care about, for example, if a is NULL, but :para is not NULL.
    Explain, using specific examples, how you get those results from that data.
    In problems like this, it often helps to know if there are impossible values for any of the columns. For example, if a is a DATE, it could be helpful to know if all dates stored in the table are after the year 1000, or, if b is a NUMBER, that it is always in the range -100 to +100.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
    If the problem includes parameters (such as :para and :parb), then post a few sets of parameters and the results you want from the same sample data for each set.
    Simplify the problem if you can. For example, instead of comparing 3 columns to 3 paramaeters, could you illustrate the problem just as well with 2 columns and 2 parameters, or even 1 column and 1 parameter?
  • 4. Re: query not returning rows (nvl)
    ascheffer Expert
    Currently Being Moderated
    LNNVL (a != :para)
    And note the "!", you should negate your logic

Legend

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