Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL query reserved word

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] SQL query reserved word
 Login/Join
 
Platinum Member
posted
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,
Tony

This message has been edited. Last edited by: Pondog,


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Virtuoso
posted Hide Post
Does this SQL code run successfully if you used DB2's own query tool or Toad?


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Expert
posted Hide Post
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.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Expert
posted Hide Post
Hi Pondog

Can you please update your first post in this thread and add [SOLVED] or [CLOSED] to the title.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL query reserved word

Copyright © 1996-2020 Information Builders