• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

PASSING A DATE PARAMETER FROM CRYSTAL 2008 TO ORACLE 11 G STORED PROCEDURE

I encountered the error failed to retrieve data from database

In crystal the data type is datetime

I have the stored procedure set up as follows:

CREATE OR REPLACE PROCEDURE get_proj_date_data
( p_proj_date1 IN DATE, cur_OUT OUT SYS_REFCURSOR)
AS
BEGIN
 OPEN cur_OUT FOR
    SELECT  
         title "Skill_No",
         trunc (p_proj_date1) "Projected Date  
FROM TABLE tbl
WHERE   trunc(tbl.eaos) > trunc(p_proj_date1)  AND trunc(tbl.prd) > trunc(p_proj_date1)                  
 
0
cookiejar
Asked:
cookiejar
  • 7
  • 5
  • 5
1 Solution
 
mlmccCommented:
Ar eyou adding the arameter or does Crystal add it based on the stored procedure?

mlmcc
0
 
cookiejarAuthor Commented:
Crystal adds it based on the stored procedure.  The Stored Procedure sets its datatype as datetime.
0
 
sdstuberCommented:
I'm not sure about your crystal interaction with the procedure,
but while you've got my attention,  this construct is not efficient

trunc(tbl.eaos) > trunc(p_proj_date1)  AND trunc(tbl.prd) > trunc(p_proj_date1)              

try modifying it like this...

tbl.eaos >= trunc(p_proj_date1)+1  AND tbl.prd >= trunc(p_proj_date1)+1              

if you have indexes on either or both of those columns, it should perform much better.
The original syntax will cause the indexes to be ignored
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mlmccCommented:
Does Oracle have a DATE datatype?  Some databases don't.

You could try editting the parameter to change it to a DATE type but I don't think you can.

mlmcc
0
 
sdstuberCommented:
yes, oracle definitely has a DATE type.  It stored date/time values down to the second.

if you need sub-second time resolution, or timezone support then you'll have to use TIMESTAMP or TIMESTAMP WITH TIME ZONE types

for the procedure above though,  DATE should be sufficient because TRUNC will "round down" the time to 00:00:00 anyway.
0
 
sdstuberCommented:
is the returned cursor empty?

what do you get if you run the sql statement yourself?  Does it return data? If not, then fetching from a cursor of the same query can't either.
0
 
cookiejarAuthor Commented:
When I run the sql statement and manually enter the date like the following, the sql returns data:

tbl.eaos > to_date('01/01/2012','MM/DD/YYYY')  AND tbl.prd > to_date('01/01/2012','MM/DD/YYYY')

However, when it is passed from Crystal I encounter an error.

By the way what does the trunc(p_proj_date1)+1 , "+1" do?  Does it add another day to the p_proj_date1?
0
 
sdstuberCommented:
yes,  +1 adds a day.  I do that,  because


trunc(tbl.eaos) > trunc(p_proj_date1)


means return all values where the day of eaos > the day of p_proj_date1

so  all values 2011-10-10 23:59:59  or earlier are  NOT included for p_proj_date1 of 2011-10-10 00:00:00 or any other value for today

In other words,  for any given day,  only return values for the NEXT day or futher into the future.  hence my rewrite as

tbl.eaos >= trunc(p_proj_date1)+1
0
 
cookiejarAuthor Commented:

Thank you.  I wish someone had a solution on how to pass the date from Crystal to the stored procedure and not encounter an error.  
0
 
cookiejarAuthor Commented:
I think I have resolved the problems. I defined the date variable in ORACLE as VARCHAR2 passing it from crystal in the format M/DD/YYYY or MM/DD/YYYY
In ORACLE, I used the To_Date function.  

I have a question,  I need to sort on the date in CRYSTAL reports but in order to sort, I need to convert to this format YYYYMMDD to get the date to sort in correct order.  How would convert for example USING a string in ORACLE or CRYSTAL:
01/01/2011 or 1/1/2011
to 20110101?
0
 
sdstuberCommented:
convert the string to date and then back to string


to_char(to_date(yourstring,'mm/dd/yyyy'),'yyyymmdd')
0
 
cookiejarAuthor Commented:
Thank you.
0
 
cookiejarAuthor Commented:
Crystal Experts:  In my cross-tab report, I am using the datesort  YYMMMDD as my group by field and using the datecolumn as my customized group name field.  When it displays in the cross-tab column, it displays for example as 1/1/2011 00:00:00.  I am unable to change the format to not display the hours minutes and seconds.  Are there any recommendations?
0
 
mlmccCommented:
Can you right click the field and format it?

mlmcc
0
 
mlmccCommented:
You could use Date({YourDateField}) as the customized name through a formula not a different field

mlmcc
0
 
cookiejarAuthor Commented:
Will the dates sort in the correct order if I use this formula field? I used to the date sort YYYYMMDD for the correct sort order? For example, if I have 1/1/2012 and 02/02/2011 will the 02/02/2011 sort before 1/1/2012?
0
 
mlmccCommented:
So long as it is a date field it should sort correctly

mlmcc
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 7
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now