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)
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)
ASKER
Crystal adds it based on the stored procedure. The Stored Procedure sets its datatype as datetime.
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
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
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
You could try editting the parameter to change it to a DATE type but I don't think you can.
mlmcc
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.
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.
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.
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.
ASKER
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/D
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?
tbl.eaos > to_date('01/01/2012','MM/D
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?
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
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
ASKER
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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.
ASKER
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?
Can you right click the field and format it?
mlmcc
mlmcc
You could use Date({YourDateField}) as the customized name through a formula not a different field
mlmcc
mlmcc
ASKER
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?
So long as it is a date field it should sort correctly
mlmcc
mlmcc
mlmcc