SQL

Create a table out of nothing


Problem

Not so much a specific problem, but in SQL you should be aware of the fact, that lots of things behave like a table. You can create records out of basically nothing.


Solution

Query records out of nothing

In most SQL databases you don't need to have a table, you can basically just write SELECT 1 and it works. In Oracle things work a bit different, let's see how we can create a result with a single number in it:

SELECT 1 FROM DUAL

This will show you the number one, but the column doesn't really have a name, let's rename it:

SELECT 1 as my_number FROM DUAL

Great, we have a virtually created table. We can now work as if it's a table

SELECT 1 as my_number FROM DUAL
UNION ALL
SELECT 2 FROM DUAL

And let's not forget that we can nest those things like a normal table:

SELECT * FROM (
   SELECT 1 as my_number FROM DUAL
   UNION ALL
   SELECT 2 FROM DUAL
)

Create sequence of numbers on the fly

Let's have a look at a hierarchical query. Maybe you just need some test data, but you need to group things by each day of the year, whatever the reason is, this query will create a recordset with numbers on the fly, from 1 to 10:

SELECT  LEVEL 
FROM DUAL
CONNECT BY LEVEL <= 10
ORDER BY LEVEL

Comments




Please sign-in to post a comment