Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

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.
0
deNZity
Asked:
deNZity
  • 3
  • 2
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now