Home » SQL & PL/SQL » SQL & PL/SQL » ORA-02287: sequence number not allowed here
ORA-02287: sequence number not allowed here [message #144624] Thu, 27 October 2005 08:19 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I've created the below insert query and receive the sequence number error when I attempt to execute:

insert into rx_dict (
rx_dict_id, drug_name, route_admin, dosage_form, rx_ident, strength, kdc, generic_code, ndc)
select dict_seq.nextval, drug_name, route_admin, dosage_form, rx_ident, strength, kdc, generic_code, max(ndc)
from temp_ndc where temp_ndc.obsolete_date is null
and
upper(rtrim(drug_name)||rtrim(route_admin)||rtrim(dosage_form)||rtrim(rx_ident)||rtrim(strength)||rtrim(kdc)||rtrim(generic_code))
not in
(select upper(rtrim(drug_name)||rtrim(route_admin)||rtrim(dosage_form)||rtrim(rx_ident)||rtrim(strength)||rtrim(kdc)||rtrim(generic_code))
from rx_dict)
group by drug_name, route_admin, dosage_form, rx_ident, strength, kdc, generic_code;

I am attempting to perform an insert on the rx_dict field where the concatenated fields of temp_ndc do not equal the concatenated fields from rx_dict. I've read that I have to put the dict_seq.nextval on the outside of the query, but I'm not sure how to do that.

Thanks.

Report message to a moderator

Re: ORA-02287: sequence number not allowed here [message #144627 is a reply to message #144624] Thu, 27 October 2005 08:36 Go to previous messageGo to next message
RahulNr
Messages: 13
Registered: September 2005
Location: india
Junior Member
Well you cannot use the sequnce in the query which has the group by or aggregated data. The solution to solve this problem is to move the sequnce out of the select. Try this,

insert into rx_dict (
rx_dict_id, drug_name, route_admin, dosage_form, rx_ident, strength, kdc, generic_code, ndc)
select dict_seq.nextval,b.* from (
select dict_seq.nextval, drug_name, route_admin, dosage_form, rx_ident, strength, kdc, generic_code, max(ndc)
from temp_ndc where temp_ndc.obsolete_date is null
and
upper(rtrim(drug_name)||rtrim(route_admin)||rtrim(dosage_form)||rtrim(rx_ident)||rtrim(strength)||rtrim(kdc)||rtrim(generic_code))
not in
(select upper(rtrim(drug_name)||rtrim(route_admin)||rtrim(dosage_form)||rtrim(rx_ident)||rtrim(strength)||rtrim(kdc)||rtrim(generic_code))
from rx_dict)
group by drug_name, route_admin, dosage_form, rx_ident, strength, kdc, generic_code) b;

Hope this helps
R

Report message to a moderator

Re: ORA-02287: sequence number not allowed here [message #144629 is a reply to message #144627] Thu, 27 October 2005 08:37 Go to previous messageGo to next message
RahulNr
Messages: 13
Registered: September 2005
Location: india
Junior Member
sorry forgot to remove the seuence.nextval from the inner query, try this

insert into rx_dict (
rx_dict_id, drug_name, route_admin, dosage_form, rx_ident, strength, kdc, generic_code, ndc)
select dict_seq.nextval,b.* from (
select drug_name, route_admin, dosage_form, rx_ident, strength, kdc, generic_code, max(ndc)
from temp_ndc where temp_ndc.obsolete_date is null
and
upper(rtrim(drug_name)||rtrim(route_admin)||rtrim(dosage_form)||rtrim(rx_ident)||rtrim(strength)||rtrim(kdc)||rtrim(generic_code))
not in
(select upper(rtrim(drug_name)||rtrim(route_admin)||rtrim(dosage_form)||rtrim(rx_ident)||rtrim(strength)||rtrim(kdc)||rtrim(generic_code))
from rx_dict)
group by drug_name, route_admin, dosage_form, rx_ident, strength, kdc, generic_code) b

Report message to a moderator

Re: ORA-02287: sequence number not allowed here [message #144630 is a reply to message #144624] Thu, 27 October 2005 08:40 Go to previous message
RahulNr
Messages: 13
Registered: September 2005
Location: india
Junior Member
Well try this,

insert into rx_dict (
rx_dict_id, drug_name, route_admin, dosage_form, rx_ident, strength, kdc, generic_code, ndc)
select dict_seq.nextval,b.* from (
select drug_name, route_admin, dosage_form, rx_ident, strength, kdc, generic_code, max(ndc)
from temp_ndc where temp_ndc.obsolete_date is null
and
upper(rtrim(drug_name)||rtrim(route_admin)||rtrim(dosage_form)||rtrim(rx_ident)||rtrim(strength)||rtrim(kdc)||rtrim(generic_code))
not in
(select upper(rtrim(drug_name)||rtrim(route_admin)||rtrim(dosage_form)||rtrim(rx_ident)||rtrim(strength)||rtrim(kdc)||rtrim(generic_code))
from rx_dict)
group by drug_name, route_admin, dosage_form, rx_ident, strength, kdc, generic_code) b;

Hope this helps

R

Report message to a moderator

Previous Topic: how to write this SQL
Next Topic: USING Clause etc.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 07:20:30 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.