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     [CLOSED] Behaviour of LST

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Behaviour of LST
 Login/Join
 
Virtuoso
posted
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 Wink

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,


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
This is exactly the behaviour expected. See Optimizing Sorts in DB2 here.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
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.


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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
quote:
SQL traces should ALWAYS be employed when doing report development against relational tables

Well said!



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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,



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
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.


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


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
Platinum Member
posted Hide Post
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
 
Posts: 164 | Registered: March 26, 2003Report This Post
Virtuoso
posted Hide Post
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


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
Platinum Member
posted Hide Post
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.



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Report This Post
Virtuoso
posted Hide Post
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.


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
Platinum Member
posted Hide Post
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
 
Posts: 164 | Registered: March 26, 2003Report This Post
Virtuoso
posted Hide Post
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.


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     [CLOSED] Behaviour of LST

Copyright © 1996-2020 Information Builders