While writing useless queries for solving exotic challenges, I realized that the use of dummy tables or unions of selects against DUAL for producing a few data-rows is a awkard solution to a problem that has a much more elegant one. Using a database type and the TABLE operator we have a very compact way at our disposal for generating records on the fly.
This means that using a simple statement like:
select *
from table ( num_tbl(1,2,3,4,5,6) )
we can generate rows with numeric values:
COLUMN_VALUE
------------
1
2
3
4
5
6
6 rows selected.
The same goes for strings: 
select *
from table( string_table('John','Mike','Tobias') )
with the following result:
COLUMN_VALUE
----------------------
John
Mike
Tobias
The only set up we need to do is create the database TYPE that will be created and queried from on the fly:
create type num_tbl as table of number/
and
create type string_table as table of varchar2(2000)/
Of course the Table operator could call a PL/SQL function that can generate more complex records according to far more complex algoritms.
Is this possible with SQL2005. I tried this but it did not work..
I have a small problem. I need to create a view to generate a report. The view has one column called month. The values for this column do not come from any database table. It is coming from the report period (ex:- 01/01/2007 to 31/01/2007) – here month is january. If it is more than one month the values for the other fields must be properly distributed. That is there will be 3 months entries in the case of three months. How can i do this?
How can i do same thing in MS-SQL 2000
Please give me anser ASAP.
I can not able to execute
select * from table ( num_tbl(1,2,3,4,5,6) ) in MS-SQL
Please tell me what is correct syntax in MS-SQL ?
Thankx in Advance
Because there no condition after connect by. SQL> select rn from ( 2 select rownum rn 3 from dual 4 connect by rownum select rn from ( 2 select level rn 3 from dual 4 connect by level
mydb> select rownum
2 from dual
3 connect by rownum;
connect by rownum
*
FOUT in regel 3:
.ORA-00920: invalid relational operator
Verstreken: 00:00:00.04
mydb>
hhhm… this doesn’t work here. Odd.
Thanks for all your comments. My reactions:
– Marco: the query against DUAL would be something like
SELECT ‘Tobias’ FROM DUAL
UNION
SELECT ‘Mike’ FROM DUAL
UNION
SELECT ‘John’ FROM DUAL
etc.
I find that awkward.
– Francois: good point – although I probably would never remember the exact TYPE.
– Edwin: this would work but basically only for a set of numbers counting from 1 to somewhere. If you want to query strings or a specific set of numeric records, this does not help.
in the sector “useless queries”, my five cents
select rownum
from dual
connect by rownum
Hello,
You can obtain the same result without creating any type, but using existing ones:
SELECT * FROM TABLE( sys.dbms_debug_vc2coll (‘John’,’Mike’,’Tobias’) ) ;
Could you explain why using DUAL is an awkward solution for the problem you demonstrated? Using the dummy table DUAL is, as you know, optimized for performance; Has this been changed; Are there equally good solutions now (like the ones which are mentioned above)?
Grz
Marco