PL/SQL SPROC Sub Query Params

From: <davedejawilson_at_my-deja.com>
Date: Thu, 09 Dec 1999 15:29:26 GMT
Message-ID: <82ohsj$5u5$1@nnrp1.deja.com>

I have structured 2 select statements with groupings that are joined by a union all. The result I am able to re-group to create a final result set which is a full outer join grouped from my original table. The final resulting statement is then placed in brackets and used in an outer select statement. This works fine as a dynamic sql statement, but as soon as I try to place the working statement into a stored proc. it fails compilation. I am also using a date range within the sub select that references my stored proc input variables. Here is a sample SPROC: Please ignore the dates being passed as strings as I currently have another problem with my report writer.

CREATE PROCEDURE SP_TEST (start_date in varchar2, end_date in varchar2, user_code in varchar2, user_name in varchar2, my_cursor in out dbsp.cursor_type) AS
BEGIN
   OPEN my_cursor for
  select
   vw_scan_info.process_date,
   sum(vw_scan_info.pages_scanned) as
pages_scanned,

   sum(vw_scan_info.documents_scanned) as
documents_scanned,
   sum(vw_scan_info.pages_rescanned) as

pages_rescanned,

   max(start_date) as start_date,
   max(end_date) as end_date,
   max(user_code) as user_code,
   max(user_name) as user_name

  from
   (select

     scan.phase_operator,
     scan.process_date,
     sum(scan.pages_processed) as pages_scanned,
     sum(batch.batch_documents) as
documents_scanned,
     max(0) as pages_rescanned,
     sum(scan.phase_cumm_proc_time) as
total_scan_seconds,
     count(*) as total_scans,
     max(0) as total_rescan_seconds,
     max(0) as total_rescans
    from
     scan,
     batch
    where
     scan.phase_name = 'Scan' and
     scan.process_date between to_date

(start_date,'yyyy-mm-dd') and to_date
(end_date,'yyyy-mm-dd') and

     scan.phase_operator = user_code and
     batch.batch_name (+) = scan.batch_name
    group by
     scan.phase_operator,
     scan.process_date

    union all
    select

     scan.phase_operator,
     scan.process_date,
     max(0) as pages_scanned,
     max(0) as documents_scanned,
     sum(scan.pages_processed) as
pages_rescanned,
     max(0) as total_scan_seconds,
     max(0) as total_scans,
     sum(scan.phase_cumm_proc_time) as
total_rescan_seconds,
     count(*) as total_rescans
    from
     scan,
     batch
    where
     scan.phase_name = 'Rescan' and
     scan.process_date between to_date

(start_date,'yyyy-mm-dd') and to_date
(end_date,'yyyy-mm-dd') and

     scan.phase_operator = user_code and
     batch.batch_name (+) = scan.batch_name

   group by
    scan.phase_operator,
    scan.process_date) vw_scan_info
  group by
   vw_scan_info.process_date
  order by
   vw_scan_info.process_date desc;
END; LINE 14 (4): PLS-00320: the declaration of the type of this expression is incomplete or malformed LINE 4 (3): PL/SQL: SQL Statement ignored

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 09 1999 - 09:29:26 CST