Problem Using UNION with NEXTVAL inside a Select

[mozgheib]

Hello,

I am using the following statement and running into problems. The message I am getting is "sequence number not allowed here". Kindly advise as how to write it. See below.

select a.orion_tank, b.OrionCrude, a.prop_modified_value,
a.DayInserted, SEQ5_CRDINV.nextval, sysdate, user
from ps_staging_data a, ps_crude_map b
where a.Orion_tank = b.OrionTank
and to_char(a.dayInserted,'dd/mm/yyyy') = to_char(sysdate, 'dd/mm/yyyy')
and a.validated = 1 and a.phd_confidence >= 100 and src_flag = 'PHD'

UNION
select a.orion_tank, b.OrionCrude, a.prop_modified_value,
a.DayInserted, SEQ5_CRDINV.nextval, sysdate, user
from ps_staging_data a, ps_crude_map b
where a.Orion_tank = b.OrionTank
and to_char(a.dayInserted,'dd/mm/yyyy') = to_char(sysdate, 'dd/mm/yyyy')
and a.validated = 1 and src_flag <> 'PHD';

 

[aryeh1010]

Try this.

Aryeh Keefe

select orion_tank, OrionCrude, prop_modified_value,
DayInserted, SEQ5_CRDINV.nextval, sysdate, user
from
(
select a.orion_tank, b.OrionCrude, a.prop_modified_value,
a.DayInserted, sysdate, user
from ps_staging_data a, ps_crude_map b
where a.Orion_tank = b.OrionTank
and to_char(a.dayInserted,'dd/mm/yyyy') = to_char(sysdate, 'dd/mm/yyyy')
and a.validated = 1 and a.phd_confidence >= 100 and src_flag = 'PHD'
UNION
select a.orion_tank, b.OrionCrude, a.prop_modified_value,
a.DayInserted, sysdate, user
from ps_staging_data a, ps_crude_map b
where a.Orion_tank = b.OrionTank
and to_char(a.dayInserted,'dd/mm/yyyy') = to_char(sysdate, 'dd/mm/yyyy')
and a.validated = 1 and src_flag <> 'PHD'
)