I'm trying create an SQL report, however, in creation, I run the query with the 100 record limit and I receive the error:
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
I've searched through the code and can't find anything wrong. I've created SQL reports before, but maybe I'm missing something. Here is the code I'm using:
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
I don't see any reserved words, however, I'm still new to SQL reports in WF and it could be something I'm overlooking. Any advice would be appreciated!
Thanks, TonyThis message has been edited. Last edited by: Pondog,
WebFOCUS 8.1.05 Windows, All Outputs
January 19, 2016, 03:48 PM
BabakNYC
Does this SQL code run successfully if you used DB2's own query tool or Toad?
WebFOCUS 8206, Unix, Windows
January 19, 2016, 03:49 PM
Pondog
Yes it does. I run it in ShowCase Query to make sure it's legit before putting it in a report.
WebFOCUS 8.1.05 Windows, All Outputs
January 19, 2016, 03:56 PM
BabakNYC
Are you loading it using an .sql file external to the focexec or is it stored in the focexec? The error points to a .sql file so I'm wondering for diagnostic purposes if the sql would run when you copy/paste it into the focexec.
WebFOCUS 8206, Unix, Windows
January 19, 2016, 04:04 PM
Pondog
Whenever I create an SQL report I copy/paste the SQL from the original source. In this instance I copied it to Notepad and from there into the report.
WebFOCUS 8.1.05 Windows, All Outputs
January 19, 2016, 04:07 PM
BabakNYC
It looks like the SQL DB2 PREPARE FOR select .... END
is causing the error. I'm no SQL expert, but I think the ORDER BY being after the END might be the issue.
WebFOCUS 8206, Unix, Windows
January 19, 2016, 04:14 PM
Waz
The SQL parser does not like END on a line by its self, it reads it as a FOCUS END command, move it to the end of the previous line.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
January 19, 2016, 04:20 PM
Pondog
Thanks Waz!! That did it!
Thanks BabakNYC for taking a look!
You guys make me grateful for the Forum!!
WebFOCUS 8.1.05 Windows, All Outputs
January 19, 2016, 04:41 PM
Waz
Hi Pondog
Can you please update your first post in this thread and add [SOLVED] or [CLOSED] to the title.