I just got a bit of a shocker when investigating the SQL generated for a LST query:

SET XRETRIEVAL = OFF
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = SQLDI//CLIENT
SET TRACEUSER = CLIENT

TABLE FILE stock
SUM LST.quantity
END

Which happily transforms into the following SQL:

SELECT MAX(T1.quantity) FROM EDADBA.stock T1;

I realise I didn't tell in what order the data should be for the last value, but I certainly didn't expect it to pick the maximum quantity from our stock! Of course I meant it to return the most recent quantity in our stock.

Yes, the query is simplified to underline the issue a bit clearer, normally I'd be interested in the latest quantity of stock for a specific article of course

Of course this result can be corrected easily enough by:

TABLE FILE stock
SUM LST.quantity
BY HIGHEST 1 timestamp
END

SQL:

SELECT T1.timestamp, MAX(T1.quantity) FROM EDADBA.stock
T1 GROUP BY T1.timestamp ORDER BY T1.timestamp DESC

(I assume only the first result of that query is used by WebFOCUS.)

However, since that will only return 1 record per timestamp (provided enough accuracy for the timestamp), that results in exactly the same as below, doesn't it?:

TABLE FILE stock
SUM quantity
BY HIGHEST 1 timestamp
END

So when do we use LST? Is it a useful and/or safe aggregation to use?
Is this behaviour particular to the DB2 connector?

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

This is exactly the behaviour expected. See Optimizing Sorts in DB2 here.

Are you saying that the expected behaviour of LST (and FST too) is to give the wrong result? Because that's exactly what it's doing if you don't explicitly limit the results to 1 record per sort-group.

Say I have this data:

 timestamp          | stock
--------------------+-------
 2011/10/19 4:30.00 |   12
 2011/10/19 4:31.00 |  150
 2011/10/19 4:32.00 |   43

The current implementation of LST for DB2 returns: MAX(12, 150, 43) = 150, the highest value in the sort-group.

As far as I can tell, the only way to get the desired result of 43, the last result in the sort-group, is to use "BY HIGHEST 1 timestamp". But then I might as well leave out LST as it doesn't do anything useful anymore at that point: MAX(43) = 43. I might as well ask for the SUM or the AVG, the answer is the same.

IMHO this is broken.

LST. and FST. are only valid in the realm of FOCUS and perhaps other non-RDBMS data sources.

As there is no really a LST or FST aggregation function in ANSI SQL (unless you use analytics) the "best" the adapter can do is to use MAX or MIN.

I think that in cases like those one has resort to using PRINT instead of SUM, so the FOCUS engine will properly determine FST and/or LST but obviously performance will take a hit.

I agree with Wep5622. This has been a problem ever since FOCUS could query relational tables. Use of FST and LST should disable optimization so WF/FOCUS can determine the correct record(s). Other FOCUS functions that cannot be translated do disable optimization, why not FST and LST? Just one more reason why SQL traces should ALWAYS be employed when doing report development against relational tables.

quote:

SQL traces should ALWAYS be employed when doing report development against relational tables

Well said!

quote:

SQL traces should ALWAYS be employed when doing report development against relational tables

I keep them on permanently when accessing rdbms tables. I can't imagine how people develop without SQL traces.

Here's something to read: Creating Reports With WebFOCUS Language > Using SQL to Create Reports > Supported and Unsupported SQL Statements.

There is no mention of LST and FST being supported or unsupported.

quote:

I can't imagine how people develop without SQL traces

Regrettably some developers think that "performance" is a problem for the DBA's to fix even though very frequently most performance issues derive from poorly-written code and not necessarily from a database standpoint (though there are things there that can obviously help).

Sadly this happens in many environments, not necessarily realated to WebFOCUS.

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

I suspect that many WebFOCUS developers wouldn't know how to obtain an SQL trace, or know what that is or what the SQL statement they would get means. I do suspect there is a high correlation between those who know how to get a trace and those who know how to read SQL :P


To get back on topic; It is true that most databases have no direct equivalent for FST. and LST.
In more recent(*) versions of the SQL standard, you get "windowing functions" though, with which such could be implemented. Database versions that support these functions are starting to get more common.

Alternatively, one could apply non-standard instructions like LIMIT n (Oracle, PostgreSQL, MySQL, etc.) or TOP n (MS SQL), combined with the right sort order.

Our DB/400 database can do "FETCH FIRST n ROWS ONLY", like so:

SELECT * FROM stock ORDER BY timestamp FETCH FIRST 5 EOWS ONLY

You'd expect there to be an equivalent "FETCH LAST n ROWS ONLY" too, but that doesn't seem to exist. I think LST on DB/400 could be implemented as:

SELECT * FROM (
   SELECT * FROM stock ORDER BY timestamp DESC FETCH FIRST 5 ROWS ONLY
) ORDER BY timestamp

(*) I realize that with "recent versions" I immediately disqualified our DB/400 database, which I was encountering this issue with.

...But even if FOCUS would use an approach as outlined above, there's still the need to provide the order from which to take the first or last record.

What's needed to make this work is the ability to add a sort-order to the TABLE FILE, that results in just an ORDER BY in SQL without the GROUP BY that FOCUS normally attaches to it. Something akin to:

TABLE FILE stock
SUM LST.quantity
BY week
SORT BY timestamp
WHERE year EQ 2011;
END

In SQL this can be achieved by using a correlated subquery, or windowing functions in more recent dialects:

SELECT week, quantity
  FROM stock T1
 WHERE year = 2011
   AND NOT EXISTS (
    SELECT 1
      FROM stock T2
     WHERE T2.year = T1.year
       AND T2.week = T1.week
       AND T2.timestamp > T1.timestamp
   )
 ORDER BY week

I suppose generating correlated subqueries from arbitrary TABLE FILE statements could get quite difficult. That's probably why IBI chose to go with MIN() and MAX() instead.

Starting with WebFOCUS 7702, there is support for subqueries directly in the MFD. This is built into the PeopleSoft Adapter, but you may be able to leverage the technology for your own purposes.

See this, e.g. PeopleSoft and Effective dates

EricH

I'm sure that's an interesting feature, but it's hardly relevant when you need the first or last record per group of some arbitrary grouping, is it? You'd have to define a subquery in your master-file for every likely combination of grouping. Not convenient, IMHO.

It also raises the question whether those subqueries are only performed when their related fields are queried, or all the time. I hope it's not the latter!

And of course, how would we apply a feature in a different adapter to our datasources? We don't use Peoplesoft here and frankly I'd hardly ever heard of them until you mentioned their adapter. The name does ring a bell though.

Well, I suppose those who use the peoplesoft adapter are more enthusiastic about that feature :P

Wep, the WebFocus Correlated SubQuery is a new 7.7.02 feature for ANY SQL adapter. See this link.

I know there are a lot of shops that have a "WEBFOCUS CODE ONLY - NO PASSTHROUGH ALLOWED ON THE PREMESIS!" rules so for those who know SQL, I can see it as useful.

But in "PASSTHROUGH IS LEGAL" shops, SQL coders worth their salt can write a block of SQL passthrough code that uses the correlated subquery (such as your example) in one tenth the time it would take modify the master file, test, re-edit, etc.

I see.
Yet, I still don't see why people are enthusiastic about this feature - it seems to have fairly limited application. As far as I can see, by having to define your subqueries on column-level, you miss out on a lot of flexibility. Maybe I don't understand this new feature correctly?

For example, using my stock table, say instead of querying for the first or last quantity on stock per week, I want to query in which warehouse that first or last quantity of stock got stored per week.
That's just a different field in the table, but the subquery wasn't defined for that field, it was defined on quantity!
The same would go for a different grouping of the data; were I to query for the last quantity of stock per month, I'd have to define a new subquery in my master file again, right?

Were I using an SQL query, swapping the fields about would be a piece of cake. If it would be possible to apply a non-grouping sort order to a TABLE FILE, same story - unfortunately we are limited to using BY HIGHEST n there.

By putting the subquery as a field-definition in the master file, the column for which the subquery was defined is locked down and you are required to define yet another subquery-field if you want to apply it to a different field or using different filtering conditions.

That seems quite a limitation to me.

I'm not saying that the concept is badly thought out or anything, it's a bit similar to having class methods in object oriented programming languages and in fact, I'm playing with a similar idea for stored procedures in my favourite database. However, it doesn't seem to apply well to the problem I'm trying to solve.

maybe I'm missing something here, but AFAICT, if you want to know in which warehouse the first or last quantity of stock got stored, you would just add warehouse to your query.

You do not need a separate sub-query on warehouse. If I'm wrong here, could you post the SQL you would need?

EricH

In SQL it would be just a different column, yes.
I get the impression (no more than that) from the linked article that you'd need to specify a subquery for each column you want to treat like this, although it would probably make more sense to put the subquery on the week column.

But even then, I'd need to repeat that for a period column:

SELECT period, quantity
  FROM stock T1
 WHERE year = 2011
   AND NOT EXISTS (
    SELECT 1
      FROM stock T2
     WHERE T2.year = T1.year
       AND T2.period = T1.period
       AND T2.timestamp > T1.timestamp
   )
 ORDER BY period

(Note: 'week' got replaced by 'period')

I think part of the problem here is that WF doesn't make it particularly easy to extract date components from a date. Yeah, you can DEFINE or COMPUTE them, but they won't translate to SQL and that's a shame, because they could. Being able to "parametrise" that for the new sub-query feature would be pretty cool; two problems solved at once.

Now our situation with dates is a little more complicated, as our database stems from times yore, when dates and datetimes were not yet a common database data-type - our dates are 8-digit integers formatted like I8YYMD (I think they used to be 6-digit before 2000) and times are 6-digit ints formatted as HHMMSS. Yes, that's pretty darn awful if you need to use them in calculations.