Home » SQL & PL/SQL » SQL & PL/SQL » insert sequence with value from other table
insert sequence with value from other table [message #293267] Fri, 11 January 2008 08:57 Go to next message
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 #293276 is a reply to message #293267] Fri, 11 January 2008 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.dbforums.com/showthread.php?p=6315372&posted=1#post6315372
Re: insert sequence with value from other table [message #293277 is a reply to message #293276] Fri, 11 January 2008 09:22 Go to previous messageGo to next message
hgriva
Messages: 17
Registered: December 2007
Junior Member
its me only
Re: insert sequence with value from other table [message #293279 is a reply to message #293267] Fri, 11 January 2008 09:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And so what did you try and what is your problem?

Also, read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: insert sequence with value from other table [message #293281 is a reply to message #293279] Fri, 11 January 2008 09:44 Go to previous messageGo to next message
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 #293284 is a reply to message #293281] Fri, 11 January 2008 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

this is giving me a error.

I don't see any.
What is your problem with the link and comments I gave?

Regards
Michel
Re: insert sequence with value from other table [message #293286 is a reply to message #293284] Fri, 11 January 2008 10:19 Go to previous messageGo to next message
hgriva
Messages: 17
Registered: December 2007
Junior Member
it is giving me ora-02287 sequence numbernot allowed here
Re: insert sequence with value from other table [message #293287 is a reply to message #293286] Fri, 11 January 2008 10:25 Go to previous messageGo to next message
touchy501
Messages: 4
Registered: January 2008
Location: Georgia
Junior Member
I believe the parentheses are causing the problem. Try:

insert into target(seq_no,fsa,rec_ct)
select SEQ_TAR.nextval, fsa, sum(sal)
from source
group by fsa

Re: insert sequence with value from other table [message #293288 is a reply to message #293287] Fri, 11 January 2008 10:42 Go to previous messageGo to next message
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 #293291 is a reply to message #293288] Fri, 11 January 2008 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is better but it is still not formatted.

Regards
Michel
Re: insert sequence with value from other table [message #293292 is a reply to message #293291] Fri, 11 January 2008 11:01 Go to previous messageGo to next message
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 #293295 is a reply to message #293292] Fri, 11 January 2008 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you call that "formatted"?

Regards
Michel
Re: insert sequence with value from other table [message #293298 is a reply to message #293267] Fri, 11 January 2008 11:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>And you call that "formatted"?
Yes, for some loose definition of "formatted".
Re: insert sequence with value from other table [message #293299 is a reply to message #293295] Fri, 11 January 2008 11:09 Go to previous messageGo to next message
hgriva
Messages: 17
Registered: December 2007
Junior Member
insert into target(seq_no,fsa,rec_ct)
select seq_tar.nextval,fsa,sum(sal) from source group by fsa;
ERROR at line 2:
ORA-02287: sequence number not allowed here
Kindly help me in this issue
Re: insert sequence with value from other table [message #293311 is a reply to message #293299] Fri, 11 January 2008 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Kindly format your post, use [code] and [/code] tags as explained in the link I posted.

Regards
Michel

[Updated on: Fri, 11 January 2008 12:03]

Report message to a moderator

Re: insert sequence with value from other table [message #293315 is a reply to message #293299] Fri, 11 January 2008 12:11 Go to previous messageGo to next message
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 #293317 is a reply to message #293315] Fri, 11 January 2008 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Both queries will not work.
Dual is meaningless in first one and so does not change to the previous query.
Second one is syntaxically wrong.

Regards
Michel
Re: insert sequence with value from other table [message #293331 is a reply to message #293267] Fri, 11 January 2008 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@hgriva

You now got an answer in another since several hours, why didn't you forward it here?

Regards
Michel
Re: insert sequence with value from other table [message #293333 is a reply to message #293317] Fri, 11 January 2008 13:14 Go to previous messageGo to next message
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 #293337 is a reply to message #293333] Fri, 11 January 2008 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Dual is not meaningless, it is required.

Of course not.

Quote:

The group by clause is what caused the problem.

Yes.

Quote:

The following is a solution that I came up with: [...] without dual, it won't work.

Remove DUAL and it will still work.
Just try it.

Regards
Michel

[Updated on: Fri, 11 January 2008 13:31]

Report message to a moderator

Re: insert sequence with value from other table [message #294139 is a reply to message #293299] Wed, 16 January 2008 10:39 Go to previous message
Michel Cadot
Messages: 64151
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
Previous Topic: Replace string to array - pl/sql
Next Topic: Joining tables???
Goto Forum:
  


Current Time: Fri Dec 09 17:18:41 CST 2016

Total time taken to generate the page: 0.24374 seconds