Re: Help with Report Design - SOLVED
Date: Tue, 7 Dec 1999 08:20:27 -0500
Message-ID: <s4q2bdbiqeg183_at_corp.supernews.com>
For anyone out there following this discussion, the problem was having subqueries in the column arguments of the select statement. While this is legal, and works fine when executed from SQL*Plus, Oracle Reports died on one of the column definitions.
The original view was:
create or replace force view cust_state_l6
as
select s1.state state,
(select count(*) from customers
where months_between(sysdate, lastdate) <= 6 and lastamt < 5 and state = s1.state) l6_0_5,from states s1;
(select count(*) from customers
where months_between(sysdate, lastdate) <= 6 and lastamt >= 5 and lastamt < 50 and state = s1.state) l6_5_49,
(select count(*) from customers
where months_between(sysdate, lastdate) <= 6 and lastamt >= 50 and lastamt < 99 and state = s1.state) l6_50_99,
(select count(*) from customers
where months_between(sysdate, lastdate) <= 6 and lastamt >= 100 and state = s1.state) l6_100p
Oracle Reports would Dr. Watson on the second column (l6_5_49), no matter what position it was in the view. The other columns worked fine, even though they were defined similarly. There were no obvious data problems in that column.
The solution was to define a function in the database:
create or replace package count_pkg is
function db_stcount(pstate IN varchar2, mindol IN number, maxdol IN number,
maxmon IN number)
return number;
end count_pkg;
create or replace package body count_pkg is
function db_stcount(pstate IN varchar2, mindol IN number, maxdol IN number,
maxmon IN number)
return number
is
retval number := 0;
begin
select count(*)
into retval
from customers
where state = pstate
and months_between(sysdate, lastdate) <= maxmon
and lastamt >= mindol and lastamt < maxdol;
return retval;
exception
when no_data_found then
return retval;
end db_stcount;
end count_pkg;
The view is then redefined as follows:
create or replace force view cust_state_l6
as
select s1.state state,
count_pkg.db_stcount(state, 0, 5, 6) l6_0_5, count_pkg.db_stcount(state, 5, 49, 6) l6_5_49, count_pkg.db_stcount(state, 50, 99, 6) l6_50_99, count_pkg.db_stcount(state, 100, 999999999, 6) l6_100pfrom states s1;
Oracle Reports has no problem with this version.
Problem solved, but not understood !!! :-)
Thanks to all who helped...
Eric Raskin
eraskin_at_paslists.com
Received on Tue Dec 07 1999 - 14:20:27 CET