Archives of the TeradataForum

Message Posted: Wed, 21 Jul 2004 @ 12:44:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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
     (THE_KEY);
     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.

	<Prev	Next>		<<First	<Prev	Next>	Last>>