quote:

(FOC1400) SQLCODE IS -199 (HEX: FFFFFF39)
: [42601] [IBM][CLI Driver][AS] SQL0199N The use of the reserved word "FO
: R" following "" is not valid. Expected tokens may include: "+ -". SQL
: STATE=42601
L (FOC1405) SQL PREPARE ERROR.
(FOC1517) UNRECOGNIZED COMMAND ORDER BY
(FOC1517) UNRECOGNIZED COMMAND 1,
(FOC1517) UNRECOGNIZED COMMAND 9,
(FOC1517) UNRECOGNIZED COMMAND 5
0 ERROR AT OR NEAR LINE 83 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

SELECT DISTINCT  
	SCHDETAIL.PROVIDER,
	SCHPROVDRS.PROVNAME,
	PATMRN,
	FRSTSCHDDT AS SCHEDDATE,
	APPTDATE,
	PRVTYPEMNE,
	APPTTIME,
	SUM ( CASE 
		WHEN CATEGORY = 1 AND SCHDETAIL.STATUS = 'ARRIVED' THEN 1 
		ELSE 0 
	END ) AS NEWARRIVED,
	SUM ( CASE 
		WHEN CATEGORY = 1 AND SCHDETAIL.STATUS = 'ARRIVED' AND PANEWLAG.VISITNUM is null and DAYS ( APPTDATE ) - DAYS ( SCHDETAIL.SCHEDDATE ) <= 14 THEN 1 
		WHEN CATEGORY = 1 AND SCHDETAIL.STATUS = 'ARRIVED' AND PANEWLAG.VISITNUM is not null and LAGDAYS <= 14 THEN 1 
		ELSE 0 
	END ) AS NEWARR14,
	CASE 
		WHEN CATEGORY = 1 AND SCHDETAIL.STATUS = 'ARRIVED' AND PANEWLAG.VISITNUM is null THEN DAYS ( APPTDATE ) - DAYS ( SCHDETAIL.SCHEDDATE ) 
		WHEN CATEGORY = 1 AND SCHDETAIL.STATUS = 'ARRIVED' AND PANEWLAG.VISITNUM is not null THEN LAGDAYS 
		ELSE 0 
	END AS COLUMN0000 
FROM 
	WAREHOUSE.RPTPERIOD RPTPERIOD
	INNER JOIN WARESCHED.SCHDETAIL SCHDETAIL
	ON 
		RPTPERIOD.VPOSTPER = SCHDETAIL.APPTPERIOD
	INNER JOIN WARESCHED.SCHPROVTYP SCHPROVTYP
	ON 
		SCHDETAIL.SCHDEPT = SCHPROVTYP.PRVTYPEDEP AND
		SCHDETAIL.PROVIDER = SCHPROVTYP.PRVTYPEPRV AND
		SCHDETAIL.TYPE = SCHPROVTYP.PRVTYPETYP
	INNER JOIN WARESCHED.SCHPROVDRS SCHPROVDRS
	ON 
		SCHDETAIL.PROVIDER = SCHPROVDRS.PROVNUM
	INNER JOIN WARESCHED.SCHLOCATNS SCHLOCATNS
	ON 
		SCHDETAIL.SCHLOC = SCHLOCATNS.SCHLOCNUM
	INNER JOIN WARESCHED.SCHDEPT SCHDEPT
	ON 
		SCHDETAIL.SCHDEPT = SCHDEPT.DEPTNUM
	LEFT OUTER JOIN WAREFOCUS.PANEWLAG PANEWLAG
	ON 
		SCHDETAIL.VISITNUM = PANEWLAG.VISITNUM
	LEFT OUTER JOIN WAREHOUSE.PROVIDERS PROVIDERS
	ON 
		SCHDETAIL.PROVIDER = PROVIDERS.PROVNUM
	 
WHERE 
	VMSOGRPNUM = 3
	AND SCHDETAIL.STATUS = 'ARRIVED'
	AND SCHDETAIL.PROVIDER NOT IN( 35528, 602529 )
	AND CATEGORY = 1
	AND SCHCLNNUM <> 145
	AND PROVBARDEP = 3
	AND ( PROVDEACTD = '1/1/0001'
	OR APPTDATE < PROVDEACTD)
	AND ( PROVTERMDT = '1/1/0001'
	OR APPTDATE < PROVTERMDT) 

GROUP BY 
	SCHDETAIL.PROVIDER,
	SCHPROVDRS.PROVNAME,
	PATMRN,
	FRSTSCHDDT,
	APPTDATE,
	PRVTYPEMNE,
	APPTTIME,
	CASE 
	WHEN CATEGORY = 1 AND SCHDETAIL.STATUS = 'ARRIVED' AND PANEWLAG.VISITNUM is null THEN DAYS ( APPTDATE ) - DAYS ( SCHDETAIL.SCHEDDATE ) 
	WHEN CATEGORY = 1 AND SCHDETAIL.STATUS = 'ARRIVED' AND PANEWLAG.VISITNUM is not null THEN LAGDAYS 
	ELSE 0 
END 
ORDER BY 
	1, 
	9, 
	5