Data Fabrication
Learning Outcomes
- Learn how to fabricate a record set from table data and literal values.
Lesson Materials
You create a sample payment table and populate it with a few rows for our example.
CREATE TABLE payment ( id NUMBER , description VARCHAR2(10) , amount NUMBER ); INSERT INTO payment VALUES ( 1, 'Book', 26.75 ); INSERT INTO payment VALUES ( 2, 'Magazine', 4.99 ); |
You can query the result set, as follows:
SELECT * FROM payment; |
It returns the following:
ID DESCRIPTIO AMOUNT
----- ---------- ----------
1 Book 26.75
2 Magazine 4.99 |
You can fabricate data by multiplying it by a fabricated set of ‘Yes‘ and ‘No‘, like the following:
SELECT * FROM payment CROSS JOIN (SELECT 'Yes' AS active FROM dual UNION ALL SELECT 'No' AS active FROM dual) ORDER BY id; |
As a rule, you fabricate data with a known unique set of literal values. That means you should use a UNION ALL operator rather than a UNION operator because you do not need to incur the expense of a sort operation.
The fabricating query returns:
ID DESCRIPTIO AMOUNT ACTIVE
----- ---------- ---------- ------
1 Book 26.75 No
1 Book 26.75 Yes
2 Magazine 4.99 No
2 Magazine 4.99 Yes |
The fabrication of data effectively uses a CROSS JOIN against a one column table to multiply existing rows. In the foregoing example, the table holds two rows of a three column record set and the fabricated table of literal values holds two rows of a one column table.
The result effectively adds the literal column value to the record set and multiply the number of rows based on number of rows in the literal set. The result is a four column record set of four rows.
