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,

Can you please send the SQL statement? I think there is a syntax error in there.

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

You could add this to your trace lines, may get you room for more characters per line:
SET TRACESTAMP=OFF

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>,

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,

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?

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.

In stead, you just could also be happy with finding the cause and solving it.

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.