INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

NULL value parameter passed from Crystal Report into Stored Procedure

NULL value parameter passed from Crystal Report into Stored Procedure

(OP)
Hi,

I am not quire sure which Crystal Report forum I should post my questions.
I haven't worked on Crystal Report for a while. Recently I have a Crystal Report which is created from Crystal Report 11 and set in SAP BusinessObjects BI Platform 4.1 Support Pack 3
Version: 14.1.3.1257. The database is ORACLE 11.
One parameter is passed from Crystal Report into Stored Procedure which is called startDate. The report is set to automatically run in the CMS and the startDate is set to null. When the null is passed into Stored Procedure, the SP will get a value from database for pulling out the data.
Now the problem is when the startDate with null value, it never pull the corrected data out, looks like the NULL is not passed into the Stored Procedure as NULL.
The logic in the SP is:

IF startDate is NULL or startDate = '' THEN
v_startDate := a value which is selected from a table
ELSE
v_startDate := startDate
-- which allow people manually select the date to run the report.

v_StartDate is used in a select statement.
Is there anyone know what could happen when a Date is set to NULL in CMS? I tried to trim the StartDate before use it, but didn't help.
Thank you very much for any suggestions and helps!!!

RE: NULL value parameter passed from Crystal Report into Stored Procedure

Is the formula set for "Default Values for Null"? Also I cannot say much about Stored Procedures,Oracle and Null dates. Maybe someone else has run into this issue.

RE: NULL value parameter passed from Crystal Report into Stored Procedure

Unfortunately, Crystal/BO will not let you pass null values for parameters to stored procs - it wants a value even if the sp will accept nulls. So, you'll have to define a default value for the parameter in Crystal and then update the sp to work based on that value instead of null.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

RE: NULL value parameter passed from Crystal Report into Stored Procedure

(OP)
kray4660, Dell, thank you so much for the reply!!

Dell, thanks a lot!!
You are right. The null can't be passed from Crystal Server into SP. But in CMS, there is a option in Prompt setting to let users set the value to null (set to null) which is really misleading.Since I haven't worked on CR for couple of years, I didn't realize that is the problem. It took me days to debug my SP.
I leaned a lot from this forum when I worked on CR, and now I got help again.
Thank you!

RE: NULL value parameter passed from Crystal Report into Stored Procedure

(OP)
Hi,

I changed the default value for the parameter "startDate" to "1/1/1900", but the report still failed to run. The error is "Can't retrieve data from database. Database Vendor Code: 20102". Is there anyone know what this error mean? I google it but have no idea what that means when it is in Crystal Report Server. Thank you so much for any helps!!!
The logic in the SP now:
IF trunc(startDate) = to_date('1/1/1900','mm/dd/yyyy') THEN
v_startDate := a value which is selected from a table
ELSE
v_startDate := startDate

RE: NULL value parameter passed from Crystal Report into Stored Procedure

That code is specific to the type of database you're connecting to, so you would look it up in the database documentation. A quick Google search indicates that this error has something to do with collections. So I would try running the stored proc outside of Crystal to determine whether it is working correctly before trying to use it in a report.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

RE: NULL value parameter passed from Crystal Report into Stored Procedure

(OP)
Dell,

Thank very much!!
I have tried to run the SP separately in TOAD, from sqlplus. All worked fine, only when it works with Crystal Report Server, the report failed to retrieve data. Inside of the Stored Procedure, it called other two Stored Procedure to get values, looks like that makes Crystal Report do not work correctly. I can't figure out why and changed the script without calling other SP to make it work for now.

RE: NULL value parameter passed from Crystal Report into Stored Procedure

Cool... When I was doing that sort of thing I ended up creating an Oracle Package that contained all of the code to get the data. That way the stored proc could still call other stored procs (inside the package) without running into these problems. I could also pre-define the record type for the result set so that Crystal wouldn't have issues with calls outside of the package.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

RE: NULL value parameter passed from Crystal Report into Stored Procedure

(OP)
Dell,
Thank you!!
I did pre-define the record type for the result set in a package. Maybe I should try using Package instead of Stored Procedure. The way that I am doing is a work around method, I want to surely solve this problem.
Thank you for the idea.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close