pass null value in parameter to oracle db

Posted on 2007-08-08
Last Modified: 2013-11-05
I am using MS reporting services sql server 2005 creating a report that needs to pass a null value through a parameter to the oracle db being queried.

have tried  "AND ip.BOOKING_CONFIRMED_IND -= nvl(:PARAM3,'NoBooking ')"  which returned nothing.
this field "BOOKING_CONFIRMED_IND" uses 'Y' or 'N' as values,
however it can be empty if a booking has not been made. It is these records I want to retrieve.
Question by:deNZity
    LVL 39

    Expert Comment



    Author Comment

    Hi thanks for the reply.
    I tried what you suggested and when I run the query through Toad with a blank value for the parameter it defaults to the 'Y' value same when I try to run report in reporting services.
    LVL 39

    Accepted Solution

    do you want to retrieve BOOKING_CONFIRMED_IND null/blank records if parameter passed is null?
    if that is the case then try this
    "AND nvl(ip.BOOKING_CONFIRMED_IND, 'NoBooking') = nvl(:PARAM3, 'NoBooking' )"  

    Author Comment

    I want to retrieve records where the booking_confirmed_ind is null(blank)
    I will try your suggestion when I'm back at the office--2 days time.
    LVL 50

    Expert Comment

    null is not blank
    null means unknown

    you have t use special syntax to test for null values...

    where columnx is null ...

    it may not be possible to pass the null through...

    have you considered
     "AND (ip.BOOKING_CONFIRMED_IND -= nvl(:PARAM3,'NoBooking ') or ip.booking_confirmed_ind is null)"  

    Author Comment

    Thanks for the replies, both solutions worked but appari was first.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    Title # Comments Views Activity
    sql query help 7 71
    how to tune this sql query 61 74
    Stored procedure 4 21
    ms sql + top 1 for each customer 3 22
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    This video shows how to recover a database from a user managed backup

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now