| insert sequence with value from other table [message #293267] |
Fri, 11 January 2008 08:57  |
hgriva
Messages: 17
Registered: December 2007
|
Junior Member |
|
|
Hi,
I have a table TARGET whose structure is
seq_no number -- should be populated with sequence SEQ_TAR
fsa varchar2(10)
Table SOURCE structure is
-------------------------
fsa varchar2(10)
my requirement is
i have to insert into the TARGET table with the fsa value from
SOURCE table along with the sequence number using sequence SEQ_TAR.nextval.
|
|
|
|
|
|
|
|
|
|
| Re: insert sequence with value from other table [message #293281 is a reply to message #293279] |
Fri, 11 January 2008 09:44  |
hgriva
Messages: 17
Registered: December 2007
|
Junior Member |
|
|
i'm once again giving the scenario
I have a table TARGET whose structure is
seq_no number -- should be populated with sequence SEQ_TAR
fsa varchar2(10)
rec_ct number
Table SOURCE structure is
-------------------------
fsa varchar2(10)
sal number
my requirement is
i have to insert into the TARGET table the fsa value from
SOURCE table along with the sequence number using sequence SEQ_TAR.nextval.
like
insert into
target(seq_no,fsa,rec_ct)
select
(SEQ_TAR.nextval,
fsa,
sum(sal)
from source
group by fsa
)
this is giving me a error.
Kindly help.
|
|
|
|
|
|
|
|
|
|
| Re: insert sequence with value from other table [message #293288 is a reply to message #293287] |
Fri, 11 January 2008 10:42 |
hgriva
Messages: 17
Registered: December 2007
|
Junior Member |
|
|
please see the message
test@ISNS> insert into target(seq_no,fsa,rec_ct)
2 select SEQ_TAR.nextval, fsa, sum(sal)
3 from source
4 group by fsa;
select SEQ_TAR.nextval, fsa, sum(sal)
*
ERROR at line 2:
ORA-02287: sequence number not allowed here
|
|
|
|
|
|
| Re: insert sequence with value from other table [message #293292 is a reply to message #293291] |
Fri, 11 January 2008 11:01 |
hgriva
Messages: 17
Registered: December 2007
|
Junior Member |
|
|
test@ISNS>INSERT INTO TARGET (SEQ_NO, FSA, REC_CT)SELECT SEQ_TAR.NEXTVAL, FSA, SUM(SAL) FROM SOURCE GROUP BY FSA;
SELECT SEQ_TAR.NEXTVAL, FSA, SUM(SAL) FROM SOURCE GROUP BY FSA
*
ERROR at line 2:
ORA-02287: sequence number not allowed here
|
|
|
|
|
|
|
|
|
|
|
|
| Re: insert sequence with value from other table [message #293315 is a reply to message #293299] |
Fri, 11 January 2008 12:11 |
touchy501
Messages: 4
Registered: January 2008
Location: Georgia
|
Junior Member |
|
|
you cannot select the nextval like that. it must come from dual or be included in a values set....examples:
This might work....
insert into target(seq_no,fsa,rec_ct)
select seq_tar.nextval,fsa,sum(sal) from source, dual group by fsa;
if not, try this:
insert into target(seq_no,fsa,rec_ct) values
(seq_tar.nextval,(select fsa,sum(sal) from sourcegroup by fsa));
|
|
|
|
|
|
|
|
| Re: insert sequence with value from other table [message #293333 is a reply to message #293317] |
Fri, 11 January 2008 13:14 |
touchy501
Messages: 4
Registered: January 2008
Location: Georgia
|
Junior Member |
|
|
Dual is not meaningless, it is required. The group by clause is what caused the problem. I created your tables in my environment and duplicated the problem. The following is a solution that I came up with:
insert into admin.target(seq_no,fsa,rec_ct)
select admin.seq_tar.nextval,fsa,rec_ct from (select fsa,sum(sal) rec_ct from admin.source group by fsa), dual;
without dual, it won't work.
|
|
|
|
|
|
| Re: insert sequence with value from other table [message #294139 is a reply to message #293299] |
Wed, 16 January 2008 10:39 |
 |
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
@hgriva
You got an answer on 01-11-08.
Don't you think it would be nice to post it here?
Do you think posting something useful is a waste of time?
Do you think we are just here to give you help and you are not there to provide it?
I love this kind of guys that asks for help and when they get the answer they let you down like an old sock (as we say in French).
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
