Failed to Open Row set

[fkavanagh]

Hi all,

I am getting this error when I am running a report from a view I created using functions as well as direct table references in Oracle. This message always follows up with another message which is quite complicated but goes like:

Query Engine Error: "HY000:[DataDirect][ODBC Oracle Wire Protocol driver][ORACLE]ORA-01861:Literal does not match format string
ORA-06512: at "COREHR.CTBI_GET_PAY_CODE_DAYS", line 31
ORA-06512: at line 1"

The CTBI_BET_PAY_CODE_DAYS is a function and line 31 states
"for c1_rec in c1 loop"

The view works no problem in SQL Plus or TOAD but creates this error in Crystal Reports 9. Anybody have any ideas?


Thank You

FK

 

[fkavanagh]

I know its another one of those Failed to Open A Rowset messages but I have searched the Knowledge base on both Crystals support site and the Tek-Tips and none of them have this problem. I have also tried to run this report connecting to Oracle using Oracle server connectivity and using CR 8.5 still to no avail.

If there is anything at all out there, any slight glimmer of a possibilty, Changing the function to return whatever or adjusting the view to convert to dates as strings etc.

Thanks

FK

 

[Katy44]

This probably won't help, but have you checked your table relationships recently? I get this message all the time and it's usually because Crystal has really helpfully added a link for me where there isn't one!

 

[Turkbear]

Hi, Please post your function's code..Something in c1 is not matching up with c1_rec --

Error Details:

ORA-01861 literal does not match format string

Cause: Literals in the input must be the same length as literals in the format string (with the exception of leading white space). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra white space.

Action: Correct the format string to match the literal.


Include what you are passing to the function as well.

 

[fkavanagh]

Hi here is the Code of the Function

CREATE OR REPLACE function ctbi_get_pay_code_days(p_empno in ct_calc_hours.person%type,
p_pay_code in ct_pay_code.pay_code%type,
p_effective_date in date,
p_no_days in number) return number as

v_num_days number := 0;
v_calc_hours number := 0;
v_pattern ct_work_pattern.pattern%type;




cursor c1 is
select round(decode(ctpc.calc_min_dec,'M',((ctch.hours - trunc(ctch.hours)) / .6) +
trunc(ctch.hours), ctch.hours),2) hours,ctch.pattern
from ct_calc_hours ctch,ct_pay_code ctpc
where ctch.day between (to_date(p_effective_date,'DD-MON-RRRR') - p_no_days) and to_date(p_effective_date,'DD-MON-RRRR')
and ctch.pay_code = p_pay_code
and ctch.person = p_empno
and ctch.pay_code = ctpc.pay_code;

cursor c2 is
select nvl(default_hours,8) default_pattern_hours
from ct_work_pattern
where pattern = v_pattern;

begin
if p_empno is not null then
if p_pay_code is not null then
if p_no_days is not null then
for c1_rec in c1 loop
v_pattern := c1_rec.pattern;
v_calc_hours := c1_rec.hours;
for c2_rec in c2 loop
if c2_rec.default_pattern_hours = 0 then
null;
else
v_num_days := v_num_days + (v_calc_hours / c2_rec.default_pattern_hours);
end if;
end loop;
v_pattern := null;
v_calc_hours := 0;
end loop;
end if;
end if;
end if;

return v_num_days;

end ctbi_get_pay_code_days;
/


As I said in the original the function and the view calling it is functioning fine in SQL Plus and TOAD or any other SQL application but I am just getting the error in Crystal.
Rgds

Fergal

 

[fkavanagh]

I found the error (or should I say the system Vendor did). It was an issue with a sysdate allright and it needed to be set to_char first and then to date.

Thanks for the suggestion I hope that this close out if you like might help someone searching in the future.

Fergal