pass null value in parameter to oracle db

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.
deNZityAsked:
Who is Participating?
 
appariCommented:
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' )"  
0
 
appariCommented:
try

"AND ip.BOOKING_CONFIRMED_IND = nvl(:PARAM3, ip.BOOKING_CONFIRMED_IND )"  
0
 
deNZityAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
deNZityAuthor Commented:
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.
0
 
LowfatspreadCommented:
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)"  
0
 
deNZityAuthor Commented:
Thanks for the replies, both solutions worked but appari was first.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.