SQLBase: creating a temporary table in the FROM clause

forum.wishlist (2000-2005) & forum.gupta.wishlist (2005-2010)
NewsgroupServer
Robot
Robot
Posts: 116488
Joined: 24 Feb 2017, 12:00
Location: World wide

SQLBase: creating a temporary table in the FROM clause

Post by NewsgroupServer » 20 Apr 2007, 14:03

 Posted by:  Alois Fuchs 

In another DBMS (SOLID) I found a very helpful feature like this:

SELECT TmpTbl1.ArtNo, TmpTbl1.Count, TmpTbl2.Count
FROM (
SELECT ArtNo, SUM( Qty ) AS Count
FROM Stock1
GROUP BY ArtNo
) AS TmpTbl1,
(
SELECT ArtNo, SUM( Qty ) AS Count
FROM Stock2
GROUP BY ArtNo
) AS TmpTbl2
WHERE TmpTbl1.ArtNo = TmpTbl2.ArtNo AND
TmpTbl1.Count != TmpTbl2.Count

In the FROM clause two temporary tables are created by a SELECT clause each and named as "TmpTbl1" and "TmpTbl2".
Within the inner SELECT clauses a SUM is calculated and GROUPed BY, and then named with "Count". This "Count" you
can use as a virtual column in the outer SELECT and WHERE clause.
In the current version of SQLBase you would have to define and create two VIEWS for the above Sub-SELECTs and then
to perform a JOIN in a SQL Statement - very more fiddly than the feature described above.

Return to “gupta.wishlist”

Who is online

Users browsing this forum: [Ccbot] and 0 guests