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