SET PAUSE OFF
SET ECHO OFF
SET NEWPAGE 0
--SET SPACE 0
SET PAGESIZE 8000
SET LINESIZE 69
SET FEEDBACK ON
SET TERMOUT OFF
SET HEADING ON

spool items_for_weeding

SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */  z30_collection, SUBSTR(Z30_CALL_NO,1,25), SUBSTR(Z30_DESCRIPTION,1,35),    SUBSTR(Z30_BARCODE,1,14),   SUBSTR(Z13_TITLE,1,45), Z35_EVENT_TYPE,    COUNT(*)
FROM law01.Z13, Z30, Z35, z103
WHERE  Z35_REC_KEY || LPAD (Z35_ITEM_SEQUENCE,6,'0') = Z30_REC_KEY AND  SUBSTR(Z30_REC_KEY,1,9) = substr (z103_rec_key,6,9)   and Z13_REC_KEY = substr(z103_rec_key_1,6,9) AND Z35_EVENT_TYPE IN ('50','52','62','63','80','82') AND Z35_SUB_LIBRARY = 'XXXXX'
HAVING COUNT(*) < '5'
GROUP BY z30_collection, SUBSTR(Z30_CALL_NO,1,25), SUBSTR(Z30_DESCRIPTION,1,35), SUBSTR(Z30_BARCODE,1,14), SUBSTR(Z13_TITLE,1,45),  Z35_EVENT_TYPE ORDER BY 1, 2, 5;

SPOOL OFF
EXIT