We used to use write simple statements in DB2 like below. But, after migrating to Oracle 11g, the script isn't working. Can anyone help the equivalent and without using UNION.
SELECT * FROM (VALUES (1,1), (1,2), (2,1), (2,2)) as T (a, b);
Error starting at line 1 in command:
SELECT * FROM (VALUES (1,1), (1,2), (2,1), (2,2)) as T (a, b)
Error at Command Line:1 Column:15
SQL Error: ORA-00903: invalid table name
00903. 00000 - "invalid table name"
into T (a, b) VALUES (1,1)
into T (a, b) VALUES (1,2)
into T (a, b) VALUES (2,1)
into T (a, b) VALUES (2,2)
select dummy from dual;
Not sure why you refuse UNION (the expression in the FROM clause seem to do it behind the scene), if there is any logic behind those values (seem to be all permutations with repetion of two numbers), there may be better ways to generate them (even with UNION).