PL/SQL SPROC Sub Query Params

From: <davedejawilson_at_my-deja.com>
Date: Thu, 09 Dec 1999 15:32:41 GMT
Message-ID: <82oi2p$68f$1@nnrp1.deja.com>

This sql statement work dynamically, but not in a stored proc. Please ignore dates passed as string, as this is done to get around my report writer problems.

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:32:41 CST