Archives of the TeradataForum
Message Posted: Wed, 21 Jul 2004 @ 12:44:09 GMT
| Subj: || || Re: Is there an equivalent of DUAL in Teradata |
| From: || || Fred Pluebell |
Others have mentioned that for simple cases you don't need a FROM clause. But if rewriting your SQL in that way isn't feasible, you can
certainly create your own one row / one column table. For best performance, you want to make certain all references will be single-AMP operations.
I suggest something such as the following:
CREATE TABLE DUAL_TBL (THE_KEY INTEGER NOT NULL) UNIQUE PRIMARY INDEX
INSERT INTO DUAL_TBL VALUES (1);
COLLECT STATS ON DUAL_TBL INDEX(THE_KEY);
CREATE VIEW DUAL_VW AS SELECT THE_KEY FROM DUAL_TBL WHERE THE_KEY = 1;
Then use the view in your FROM clause. Note that DUAL is a reserved word, so unless you always put it in quotes - "DUAL" - you'll have to
choose some other name for the view.
By the way, this method will be slightly more efficient than putting a "constant" derived table expression in your FROM clause, though unless
your system is quite busy you may not notice the difference.