| 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 #293277 is a reply to message #293276 ] |
Fri, 11 January 2008 09:22  |
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 |
Michel Cadot
Messages: 26624
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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 |
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 |
Michel Cadot
Messages: 26624
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
| 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 |
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 |
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 |
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 |
Michel Cadot
Messages: 26624
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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 |
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 |
Michel Cadot
Messages: 26624
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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 |
anacedent
Messages: 7061
Registered: July 2005
Location: +32° 58' 41.00"N -1... |
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 |
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 |
Michel Cadot
Messages: 26624
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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]
|
|
|
| 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 #293317 is a reply to message #293315 ] |
Fri, 11 January 2008 12:17 |
Michel Cadot
Messages: 26624
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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 |
Michel Cadot
Messages: 26624
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
@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 |
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 |
Michel Cadot
Messages: 26624
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
| 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]
|
|
|
| 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: 26624
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
@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
|
|
|
]
Members
Search
Help
Register
Login
Home
