Generate rows

Here you are shown how to generate rows without the use of tables

schema:scott

You can use single value SELECT to generate tables

You can use them when you need a small table for your query but you don't have permission to create tables in the database itself.

SELECT x centigrade, x*9/5+32 fahrenheit
  FROM  (SELECT 0 x UNION SELECT 10 UNION SELECT 20
                    UNION SELECT 30 UNION SELECT 40) t

SELECT x centigrade, x*9/5+32 fahrenheit
  FROM (      SELECT  0 x FROM dual
        UNION SELECT 10   FROM dual
        UNION SELECT 20   FROM dual

       UNION SELECT 30   FROM dual
       UNION SELECT 40   FROM dual) t

In oracle you can not miss the FROM in a query so therefore FROM dual has to be used.

SQL HACKS

Hack 10 Converting subqueries into joins

Hack 11 Converting aggregate subqueries into joins

Hack 14 Generate Combinations

Hack 16 Search for a String across columns

Hack 24 Multiply Across a Result Set

Hack 25 Keep a running total

Hack 25.5 Splitting and combining columns

Hack 26 Include the rows your JOIN forgot

Hack 30 Calculate the maximum/minimum of two fields

Hack 33 Get values and subtotals in one shot

Hack 50 Combine tables containing different data

Hack 51/52 Display rows as columns

Hack 55 Import Someone Else's Data

Hack 62 Issue Queries Without Using a Table

Hack 63 Generate rows without tables

Hack 70 Combine your queries

Hack 72 Extract a subset of the results

Hack 78 Break it down by Range

Hack 88 Test two values from a subquery

Hack 98 Find and stop long running queries

Hack 100 Run SQL from a Web Page