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     [CASE OPENED] Limit on SQL length?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE OPENED] Limit on SQL length?
 Login/Join
 
Virtuoso
posted
I have this SQL query that worked just fine until I added an extra join. Suddenly it started telling me:
 (FOC1400) SQLCODE IS -199 (HEX: FFFFFF39)
 Exiting due to Exit on Error...
 : [42601] Keyword BY not expected. Valid tokens: FOR USE SKIP WAIT WITH FE
 : TCH ORDER UNION EXCEPT OPTIMIZE.
 L    (FOC1405) SQL PREPARE ERROR.


Of course I made sure I added the new column from the join to the GROUP BY (and ORDER BY) clauses after adding it to the select-list.

I copied the query into a different tool and it runs just fine!

The query is executed like this:
ENGINE EDA SET DEFAULT_CONNECTION AS400

-REMOTE BEGIN

ENGINE DB2 SET DEFAULT_CONNECTION SQL400
APP HOLD SQLREPORTS

SQL DB2 PREPARE VOI00061H_SQLOUT FOR
...long query here...
END

TABLE FILE VOI00061H_SQLOUT
PRINT *
ON TABLE HOLD AS VOI00061H FORMAT FOCUS INDEX YEAR WEEK WAREHOUSE PRODGRP PRLC CCGR CART
END

-REMOTE END
-RUN


If I remove the ORDER BY line from above query it runs alright again (and faster, so I'll probably stick with the shorter version).

Which makes me wonder, the query in its current form is 1239 bytes long. When I add the ORDER BY clause in again, it's 1322 bytes long. Am I crossing some internal buffer-limit in WebFOCUS again?

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


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Gold member
posted Hide Post
Can you please send the SQL statement? I think there is a syntax error in there.


WebFOCUS 7.6, 7.7
Windows, All Output formats
 
Posts: 90 | Location: Stuttgart | Registered: October 20, 2010Report This Post
Virtuoso
posted Hide Post
I don't think I am allowed to post the actual SQL statement here. Confidentiality...

As I said, the SQL statement copied to a different tool (SquirrelSQL) runs just fine. The only change I make is to substitute the one parameter (&YYMD) with its value (20120629).
There's clearly nothing wrong with the statement.

Is there some way to see what SQL statement WebFOCUS sent to the database? I tried some variations of the below, but it clips the output after 80 or so characters per line and continues on the next line several characters onward - the output is missing bits and pieces:
SET XRETRIEVAL = OFF
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = SQLDI//CLIENT
SET TRACEUSER = CLIENT


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Guru
posted Hide Post
You could add this to your trace lines, may get you room for more characters per line:
SET TRACESTAMP=OFF


(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)
 
Posts: 391 | Location: California | Registered: April 14, 2003Report This Post
<JG>
posted
Posting an SQL statement should not be an issue.

Change Table names, change Column names.

What is not needed is the actual request but what is needed the syntax of the request.

The DB2 specialists who view the forum require the syntax not the physical content.

quote:
(SquirrelSQL)
is no better than many other third party tools.

Some allow errors to pass and correct them where they can (not always successfully), others do not.

To get an untrucated trace send it offline

FILEDEF FSTRACE DISK FSTRACE.TRC
-RUN
SET TRACEOFF=ALL
SET TRACEUSER="path to the fstrace.trc file depending on your system, fstrace.trc is required"
SET TRACEON=SQLAGGR//FSTRACE
SET TRACEON=STMTRACE//FSTRACE
SET TRACEON=STMTRACE/2/FSTRACE
SET TRACESTAMP=OFF
-RUN

There are many other things that you can do with tracing. Read the Adapter documentation.

This message has been edited. Last edited by: <JG>,
 
Report This Post
Virtuoso
posted Hide Post
quote:
Read the Adapter documentation.

I'd love to, but... Firstly I didn't expect this to be in the adapter documentation. Secondly, where in heavens is it?!? I tried searching the ECL and the tech-doc library specifically, but the only adapter documentation I can find is "iWAF Adapter for Salesforce 7.7.0[34]"!
I'm not blaming you, but perhaps IBI should change making their documents searchable into making them findable... I can never find anything in there. Let's end the rant with that.

Regarding the trace; I don't suppose there's a switch to continue the trace through a remote call? I can of course put the trace on the remote machine, but I'd rather not clutter that file-system with trace files and such.

My attempt at anonymifying (is that a word?!?) the query is below. It's a fairly complicated query, as you can see.
ENGINE EDA SET DEFAULT_CONNECTION AS400

-REMOTE BEGIN

ENGINE DB2 SET DEFAULT_CONNECTION SQL400
APP HOLD SQLREPORTS

SQL DB2 PREPARE SQLOUT FOR
WITH cte1 AS (
	SELECT COL1, COL2, COL3, COL4, DATE1
	  FROM TABLE1
	 WHERE DATE1 > 0 AND DATE1 <= &YYMD
	   AND CATG1 IN ('01', '02', '03', '04', '05', '14')
	   AND COL5 <> 'QQ'
	   AND COL7 <> 0
)
SELECT CATG3, COL1, COL2, COL3, CATG2,
	   COL4, DESC1,
	   DATE1, t2.YEAR, t2.WEEK,
	   SUM(QTY1), SUM(QTY2),
	   SUM(QTY3), SUM(QTY4)
  FROM cte1 c1
	INNER JOIN LIB1/TABLE2 t2 ON (c1.DATE1 = t2.DATE)
	INNER JOIN LIB2/TABLE3 t3 ON (c1.COL1 = t3.COL1)
	INNER JOIN TABLE4 t4 ON (
			c1.COL1 = t4.COL1
		AND c1.COL2 = t4.COL2
		AND c1.COL3 = t4.COL3
	)
	INNER JOIN TABLE5 t5 ON (c1.COL4 = t5.COL4)
	LEFT OUTER JOIN (
		TABLE6 t6
		INNER JOIN LIB1/TABLE2 c
		   ON (
				c.YEAR = t6.YEAR
			AND c.WEEK = t6.WEEK
			AND c.DATE = &YYMD -7
		   )
		)  ON (
				c1.COL1 = t6.COL1
			AND c1.COL2 = t6.COL2
			AND c1.COL3 = t6.COL3
			AND c1.COL4 = t6.COL4
		   )
 WHERE c1.COL1 NOT IN ('AA', 'BB') AND c1.COL2 NOT IN ('XX', 'YY')
   AND NOT EXISTS (
	SELECT 1
	  FROM cte1 c2
	 WHERE c2.COL1 = c1.COL1
	   AND c2.COL2 = c1.COL2
	   AND c2.COL3 = c1.COL3
	   AND c2.COL4 = c1.COL4
	   AND c2.DATE1 > c1.DATE1
   )
 GROUP BY CATG3, COL1, COL2, COL3, CATG2, COL4, DESC1, DATE1, t2.YEAR, t2.WEEK
 ORDER BY COL4, DESC1, t2.YEAR DESC, t2.WEEK DESC, CATG3, COL1, COL2, COL3, CATG2
;
END

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS YADAYADA FORMAT FOCUS
END

-REMOTE END


Commenting the ORDER BY on the last line of the query out makes it go through, leaving it in results in the aforementioned error.

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


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
1. This won't solve your stated problem, but
quote:
c.DATE = &YYMD -7

is not the way to specify a 7-day lag. -- When run today (July 2)
&YYMD -7
equals 20120695, which presumably won't match c.DATE in any row. -- Instead use AYMD() to compute a separate &var with the lagging date, and use that in the join condition.

2. Is there a reason why the GROUP BY field list and ORDER BY list are in different order, and the latter omits DATE1?
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
O.M.G... I found the cause on a hunch and it makes me sad that I was right.

The query is fine, nothing wrong with it. I ran it in strsql on the green screen (lots of copying and pasting bits of SQL to make it fit a screen a time, PgDown, next copy-paste action. That's why I tend to use SquirrelSQL instead).
The query ran just fine.

The problem is that WebFOCUS cannot handle tabs in SQL!

I think I've run into this particular occurrence of the issue before, I even reported some of them. Someone at IBI should be really embarrassed about that!
Whoever you are, do us all (yourself included) a favour and order yourself a book on language parsing, for example: http://shop.oreilly.com/product/9781565920002.do

There is absolutely no excuse for bugs like these in a product that's been around for 20 years. The technology to handle this properly has been around since about 1975.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
In stead, you just could also be happy with finding the cause and solving it.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
And run into it again over and over? No thanks.
Stupid bugs like these cost far too many people far too much time, for no good reason at all.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report 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     [CASE OPENED] Limit on SQL length?

Copyright © 1996-2020 Information Builders