Re: Exists Tupel Queries ?
At 09:16 AM 01/28/2000 +0000, Obnoxio The Clown wrote:
>Ah, bugger! I suppose this means I have to read *another* manual. :->
Actually, it's worse than you think.
First, you can create a UDR that returns a SET/MULTISET/LIST result, and
drop the function into the from clause as follows. (the Period type BTW is
a T-SQL construct. It represents a fixed interval in the time-line i.e.
Begin/End. This is useful because in IDS.2000 you can use R-Trees to index
Overlap()/Contains() etc.)
CREATE ROW TYPE Quarter (
Year INTEGER NOT NULL,
Quarter INTEGER NOT NULL,
Range Period NOT NULL
);
GRANT USAGE ON TYPE Quarter TO PUBLIC; --
CREATE FUNCTION Quarters ( Year INTEGER )
RETURNING SET(Quarter NOT NULL)
DEFINE rtQuart SET(Quarter NOT NULL);
INSERT INTO TABLE(rtQuart)
SELECT ROW( Year,
S.Num,
Period( (S.Start || '/' || Year)::DATE,
(S.End || '/' || Year)::DATE)
)::Quarter
FROM TABLE( SET{ROW( 1, '01/01', '03/31'),
ROW( 2, '04/01', '06/30'),
ROW( 3, '07/01', '09/30'),
ROW( 4, '10/01', '12/31')
}::SET(ROW(Num INTEGER,
Start LVARCHAR,
End LVARCHAR) NOT NULL)
) S; RETURN rtQuart;
END FUNCTION;
--
SELECT Q
FROM TABLE(Quarters(1998)) Q;
You can also drop an entire SQL query into the FROM clause. This is
useful for "double aggregate" queries. Like "How many departments do we
have where the sum of their employee's salaries exceeds the department's
budget?". Of course, you can do this with a TEMP table today (and in fact
that's usually what the query plan looks like) but bundling the whole thing
into a single query has its advantages.)
SELECT COUNT(*)
FROM TABLE(MULTISET(SELECT O.Merchandise,
SUM(O.NumberBought)
FROM Order_Line_Items O, Orders D
WHERE D.OrderedOn > TODAY - 7
AND D.Id = O.Order
GROUP BY O.Merchandise
HAVING SUM(O.NumberBought) > 100
)
) B ( M, S );
One of the things that happens when you build an extensible data
management framework is that the nature of what makes up a database kind of
changes. Instead of accessing data stored in the database, you can use the
ORDBMS to access all kinds of data "through" the database. You still have
tables and blob spaces, but the kinds of things you can write queries on
changes. For example:
CREATE TABLE TmpDirectoryOF TYPE File_System
USING File_System (root="/tmp");
SELECT file_name, block_cnt FROM TmpDirectory;--
SELECT file_name, block_cnt FROM TmpDirectory WHERE block_cnt > 100;
In this example, the TmpDirectory "TABLE" is really a kind of "gateway"
to the file-system, and each row you get back from this query corresponds
to a file in the specified root directory ("/tmp in this example).
Similarly, you can get something like the XP "external" tables in the same way.
IDS.2000 isn't a DBMS. It does everything a DBMS does. But some folk are
using it as a kind of "query-centric middle-ware", where none of the data
they write SQL over is actually stored in the DBMS at all.
Kind of neat . . . . .
KR
Pb
=====================================================
Paul Brown - Chief Plumber ^..^
brown@informix.com (oo) -- Oink!
20th Floor
300 Lakeside "Sometimes, the most lost & wasted
Oakland CA 94612 take up with the clocked and the timed."
(510)628.3765 (ph) -- Woodie Guthrie
(510)835.1325 (fx)
(510) 841 5478 (home)
=====================================================