SET PAGESIZE 0
SET LINESIZE 250
SET HEADING OFF
SET FEEDBACK OFF
SET TRIM ON
SET TRIMSPOOL ON
SET TERMOUT OFF
SPOOL the_uff_report.lst
COLUMN FILE_NAME FORMAT A17
COLUMN DFC_SET FORMAT A16 FORMAT A17
COLUMN SAM_SET FORMAT A8 FORMAT A17
COLUMN FILE_NAME FORMAT A17
COLUMN PNFS FORMAT A64
SELECT D.FILE_NAME, DFC_SET, DFC_TIME1, DFC_TIME2, DFC_TIME3,
                    SAM_SET, SAM_TIME1, SAM_TIME2, SAM_TIME3,
       E.FILE_NAME PNFS
FROM (
  SELECT FILE_NAME, FILESET_NAME DFC_SET,
	 DECODE(CREATION_TIME,NULL,'-----unknown----',
	        TO_CHAR(HDWDB.UNIX_TO_DATE(CREATION_TIME),
		        'YYYYMonDD-HH24MISS')) DFC_TIME1,
	 DECODE(CREATE_DATE,NULL,'-----unknown----',
	        TO_CHAR(HDWDB.UNIX_TO_DATE(CREATE_DATE),
		        'YYYYMonDD-HH24MISS')) DFC_TIME2,
	 DECODE(UPDATE_DATE,NULL,'-----unknown----',
	        TO_CHAR(HDWDB.UNIX_TO_DATE(UPDATE_DATE),
		        'YYYYMonDD-HH24MISS')) DFC_TIME3
  FROM FILECATALOG.CDF2_FILES
  jOIN HERBER.UNMATCHED_FILESET_FILES USING(FILE_NAME)) D,
( SELECT FILE_NAME, SAM.PARAM_VALUE SAM_SET,
	 DECODE(START_TIME,NULL,'-----unknown----',
	        TO_CHAR(START_TIME,'YYYYMonDD-HH24MISS')) SAM_TIME1,
	 DECODE(CREATE_DATE,NULL,'-----unknown----',
	        TO_CHAR(CREATE_DATE,'YYYYMonDD-HH24MISS')) SAM_TIME2,
	 DECODE(UPDATE_DATE,NULL,'-----unknown----',
	        TO_CHAR(UPDATE_DATE,'YYYYMonDD-HH24MISS')) SAM_TIME3
  FROM DATA_FILES
  JOIN DATA_FILES_PARAM_VALUES USING(FILE_ID)
  LEFT JOIN PARAM_VALUES SAM USING(PARAM_VALUE_ID)
  WHERE PARAM_TYPE_ID = (SELECT PARAM_TYPE_ID
			  FROM PARAM_TYPES
			 WHERE PARAM_CATEGORY_ID = (
				 SELECT PARAM_CATEGORY_ID
				 FROM PARAM_CATEGORIES
				 WHERE PARAM_CATEGORY = 'cdf')
			   AND PARAM_TYPE = 'fileset')) S,
  HERBER.EN_FILES E
WHERE D.FILE_NAME = S.FILE_NAME
  AND D.FILE_NAME = E.FILE_BASE
ORDER BY FILE_NAME, PNFS
/
quit
