NULL value parameter passed from Crystal Report into Stored Procedure

[kate8]

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!!!

 

[kray4660]

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.

 

[hilfy]

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

http://www.decisionfirst.com

 

[kate8]

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!

 

[kate8]

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

 

[hilfy]

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

http://www.decisionfirst.com

 

[kate8]

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.

 

[hilfy]

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

http://www.decisionfirst.com

 

[kate8]

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.