Results 1 to 3 of 3
  1. #1
    Join Date
    May 2011
    Posts
    6

    Unanswered: Query not returning rows with NVL

    I'm using PHP to run SQL queries on an Oracle 9g database

    Here's the query:

    Code:
    select code, hospital, city, province, country, PHONE#
    from hospitals
    where city = 'Ottawa'
    and phone = nvl(:phone, phone)
    or

    Code:
    and phone = phone
    If the bind is not null, use it in the query. Otherwise phone = phone.
    The issue is that "phone = phone" does not return rows that have a null phone number.

    This works just dandy on non-null entries. Is there any way to force it to return the null ones when using "phone = phone"?



    Worst comes to worst I'll cough up a regular expression to remove the clause entirely.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select code, hospital, city, province, country, PHONE#
    from hospitals
    where city = 'Ottawa'
    and (phone = nvl(hone, phone)
    or phone is 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.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Is there any way to force it to return the null ones when using "phone = phone"?
    No, it is NULL, not TRUE.

    However you may simply state required conditions in SQL without messing with NVL:
    Code:
    select code, hospital, city, province, country, PHONE#
    from hospitals
    where city = 'Ottawa'
    and (:phone is null or :phone = phone)

Posting Permissions

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