UNION ALL Restrictions

I was asked to develop something where I had to use UNION ALL to combine different sub queries and also sequence in each sub query. But ironically, i ended up with error saying i cant use sequence with union all.

Here is the antidote to resolve the issue.

Let me share the sample structure,

insert into t

select a.nextval, b from t1

union all

select a.nextval, c from t2

The above fails to execute as we cant use sequence with UNION ALL.

we can do the above task with the below one.

insert into t

select a.nextval, b from

(select b from t1

union all

select c from t2)

Thats it..that solved the issue...

So the conclusion is, we cant use sequence with the following cases

• In a WHERE clause
• In a GROUP BY or ORDER BY clause
• In a DISTINCT clause
• Along with a UNION or INTERSECT or MINUS
• Ina sub-query