I went on a goose chase today - SQL trace showing problems with a query using SK001, SK002, VB001....

Since we don't use those variable naming conventions, and I couldn't find them in a search of our focexec directory, I assumed it just *couldn't* be our code Wrong! How - specifically - does this SQL translation occur?

From what I can tell it occurs when IF-THEN-ELSE optimization takes place when the aggregation can totally be handled by the RDBMS?

We always strive to have the joins passed to DB2, but often (most times) the aggregation cannot be passed. Is this a result of total aggregation being handled? Where does SK001, SK001 come from - are these internal SQL/DB2 variables, or FOCUS created variables? Is this the equivalent of column notation? It really threw me for a loop looking at code that only seemed marginally like my own

Thanks!

WebFOCUS 7.6.1
AIX

I assume, that the sql trace you're speaking of is the db2 sql trace and not the WebFOCUS trace?

You can quite easily find out what sql code is being generated by WebFOCUS by issuing the appropriate tracing commands in your fex. In combination with the SET XRETRIEVAL=OFF, you will not even access the database engine, but only generate the sql. This might shed some more light on your 'goose chase'.
If you need to know how to activate the traces, search the forum. There are a lot of posts dealing with tracing....

Sorry GamP - yes - this is the SQL trace I'm looking at. That was the reason I couldn't find the fields in question - because they were not in my focexec - they were being generated as part of the SQL being passed to DB2.

While we don't have access to run server traces, we do run SQL traces as well as -ECHO &ALL on just about every focexec and are familiar with them.

I've seen this before and I don't think you have to worry about it, unless you get SQL trace warning/error messages regarding aggregation, joins, etc.

It think this happens when the join gets complex. You could try rearranging the join structure...