Home » SQL & PL/SQL » SQL & PL/SQL » insert (merged)
insert (merged) [message #308481] Mon, 24 March 2008 08:41 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
how can i insert using select
INSERT INTO OT
(TP,MON,SPI) 1,'JAN',select SP_ID from sp
where 
sp.SP_ID in(select ds.FK_SP_ID from DET_SP ds
where sp.SP_ID=ds.FK_SP_ID) 

thanxs
Re: insert [message #308487 is a reply to message #308481] Mon, 24 March 2008 09:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How would you select constants in a regular select?

1, select col from table ?
or
select 1, col from table ?
Re: insert [message #308493 is a reply to message #308487] Mon, 24 March 2008 09:45 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
i am inserting only one value that is spi from select sp_id rest

is the value sent ..
cannot i select more then one select statement for insert into table


TP, MON, ,SPI
1 jan
for SPI
select SP_ID  from sp where sp.SP_ID in  
                  (select ds.FK_SP_ID from DET_SP ds     
                    where    sp.SP_ID=ds.FK_SP_ID);
Re: insert [message #308500 is a reply to message #308493] Mon, 24 March 2008 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No but you can select many columns in one select.

Regards
Michel
Re: insert [message #308503 is a reply to message #308500] Mon, 24 March 2008 10:07 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
got it
select 1,'JAN' ......

any way thanxs ...

hi michel still iam working on that proc and package
hope that resolve by other way ..
Re: insert [message #308505 is a reply to message #308503] Mon, 24 March 2008 10:26 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
iam getting at 8th line
ORA-00933: SQL command not properly ended


insert into bs
	(
	sec,tr_dt,rv,sp
	) 
	select
	'Tested' as sec,
	v_dt tr_dt,
	sp_id FROM Sp 
   WHERE sp_id IN (SELECT fksp_id FROM CUST 
	WHERE fksp_id=sp_id),
	count(1) rv
	from cust c where sub_type = 1
Re: insert [message #308507 is a reply to message #308505] Mon, 24 March 2008 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please take the SQL Reference and carefully read the syntax and examples.
Dont try to guess the synrax.

Regards
Michel
Re: insert [message #308512 is a reply to message #308507] Mon, 24 March 2008 10:56 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
iam looking at the sql refernce

i think we cannot use sub query for this

[Updated on: Mon, 24 March 2008 10:57]

Report message to a moderator

Re: insert [message #308514 is a reply to message #308512] Mon, 24 March 2008 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes this can be done.
Just find the single select that gives you the result you want.

Regards
Michel
Re: insert [message #308516 is a reply to message #308514] Mon, 24 March 2008 11:18 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
when i run the query i get data ..
Re: insert [message #308517 is a reply to message #308516] Mon, 24 March 2008 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which query?

Regards
Michel
Re: insert [message #308518 is a reply to message #308517] Mon, 24 March 2008 11:27 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
select sp_id FROM Sp 
   WHERE sp_id IN (SELECT fksp_id FROM CUST 
	WHERE fksp_id=sp_id);
Re: insert [message #308520 is a reply to message #308518] Mon, 24 March 2008 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Isn't this just a part of what you want to insert?

Regards
Michel
Re: insert [message #308535 is a reply to message #308518] Mon, 24 March 2008 13:57 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is this piece of code supposed to do?
...
(SELECT fksp_id FROM CUST 
 WHERE fksp_id=sp_id),
 count(1) rv                    --> this two lines are especially
 from cust c where sub_type = 1 --> interesting
Re: insert [message #308605 is a reply to message #308535] Tue, 25 March 2008 01:00 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
SQL> declare
    v_osp number(5);
    v_mtsp number(5);
   begin
    select sp_id into v_osp from   sp
   where sp_id in(select fk_spi from ot
    where fk_spi=sp_id);
   select sp_id into v_mtsp from   sp
    where sp_id in(select fk_msp from mt
   where fk_msp=sp_id);
   INSERT INTO det_sp
   (temp_sp,name,act_rec)
   (v_mtsp, 'JAN','Y');
 INSERT INTO det_sp
   (temp_sp,name,act_rec)
   ( v_osp,'JAN','Y' );
   commit;
   end;
   /
(v_mtsp, 'JAN','Y');
 *

ERROR at line 13:
ORA-06550: line 13, column 2:
PL/SQL: ORA-00928: missing SELECT keyword
ORA-06550: line 11, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 16, column 3:
PL/SQL: ORA-00928: missing SELECT keyword
ORA-06550: line 14, column 1:
PL/SQL: SQL Statement ignored
Re: insert [message #308609 is a reply to message #308605] Tue, 25 March 2008 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again read INSERT syntax don't try to guess.

Regards
Michel
variable insert [message #308697 is a reply to message #308481] Tue, 25 March 2008 04:12 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
for every records i need insert temp_sp id COMING from sp_id from sp through v_Cot variable

temp_sp,	name	act_rec
101	Curtest	Y
102	Curtest	Y
103	Curtest	Y
108	Curtest	Y
109	Curtest	Y

DECLARE 
v_Cot NUMBER(6); 
CURSOR Cot IS select sp_id  from   sp
where sp_id in(select fk_spi from ot
where fk_spi=sp_id); 
BEGIN 
OPEN cot; 
FETCH Cot INTO v_Cot; 
INSERT INTO det_sp (temp_sp,name,act_rec) values(v_cot, 'curtest','z');
EXIT WHEN Cot%NOTFOUND; 
END LOOP; 
COMMIT; 
CLOSE Cot; 
end;
/
Re: variable insert [message #308699 is a reply to message #308697] Tue, 25 March 2008 04:14 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
You have not asked a question. You have made a statement. What's your point?
Re: variable insert [message #308703 is a reply to message #308697] Tue, 25 March 2008 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is the new way of posting.
Just put the code, guess the question and answer it.

Regards
Michel
Re: variable insert [message #308714 is a reply to message #308703] Tue, 25 March 2008 04:48 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Ahhh, lol.
Re: variable insert [message #308721 is a reply to message #308714] Tue, 25 March 2008 05:13 Go to previous message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
the statement as a question is so confusing
Previous Topic: Display out put format in SQL *plus
Next Topic: ORA-12801: error signaled in parallel query server
Goto Forum:
  


Current Time: Sat Dec 10 05:17:44 CST 2016

Total time taken to generate the page: 0.06970 seconds