Re: Help with Report Design - SOLVED

From: Eric Raskin <eraskin_at_paslists.com>
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,

(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
from states s1;

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_100p
from 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

Original text of this message