OraFAQ Forum: SQL & PL/SQL » Help with insert

Home » SQL & PL/SQL » SQL & PL/SQL » Help with insert (11.2.0.3, 64-bit, Windows 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help with insert [message #645551]

Tue, 08 December 2015 10:19

sant_new1
Messages: 46
Registered: June 2014

Member

Hi friends,

Below insert fails with ora-00907 when I use order by in the sub query... Table tab1 has 28 columns to be inserted so I did not post
the entire script..

Insert into tab1  (sign_no,...,s_type,...)
select
's' || cs.content_no,
...,
(select s.sign_type from s_det s
where s.curr_type = (select min(v.curr_type) from v_det v order by v.content_no))  S_TYPE,
...
from content_sales cs;

ERROR at line 16:
ORA-00907: missing right parenthesis

Below are few records from v_det table. For each content_no, we need to compare with min(v.curr_type) and s.curr_type
in the above where clause.

SQL> select * from v_det;

CONTENT_NO           CURR_TYPE
-------------------- --------------------
J23                  235541231
J23                  235541234
A35                  6712343
A34                  6712343

Please help.. Thank you all so much.

Report message to a moderator

Re: Help with insert [message #645552 is a reply to message #645551]

Tue, 08 December 2015 10:55

John Watson
Messages: 9002
Registered: January 2010
Location: Global Village

Senior Member

The ORDER BY can't have any effect. Don't include it.

Report message to a moderator

Re: Help with insert [message #645553 is a reply to message #645552]

Tue, 08 December 2015 11:32

sant_new1
Messages: 46
Registered: June 2014

Member

If I remove order by and use just '(select min(v.curr_type) from v_det v) , there are no errors when I run the insert but then tab1.stype is all null values because the inner select query returns multiple rows for min(v.curr_type). Here is the business logic we need to use for inserting into tab1..

most of the values for columns in tab1 will be from table CS.
Logic For tab1.s_type = " s_det.sign_type where s.curr_type = v.curr_type" ( If there are multiple v.curr_type for each v.content_no, then take min(v.curr_type)"

Below are the tables with just the columns needed for this insert of s_type value.

SQL> select * from v_det;
CONTENT_NO           CURR_TYPE
--------------------                -------------
A34                            6712343
J23                            235541231
J23                            235541234
A35                            6712343
 
SQL> select * from s_det;
SIGN_TYPE            CURR_TYPE
--------------------           --------------------
TKT1                      235541231
TKT9                      6712343
 
SQL> select * from content_sales;
CONTENT_NO
--------------------
A34
A35
J23
 
I removed the insert of other columns in tab1 for ease..
 
Insert into tab1  (sign_no,s_type)
select
's' || cs.content_no,
(select s.sign_type from s_det s
where s.curr_type = (select min(v.curr_type) from v_det v))  S_TYPE
from content_sales cs;

Result of tab1 should be
SQL> select * from tab1;
SIGN_NO S_TYPE
-------------------- --------------------
sA34 TKT9
sA35 TKT9
sJ23 TKT1

Thanks for all your help..Appreciate it a lot.

[Updated on: Tue, 08 December 2015 11:33]

Report message to a moderator

Re: Help with insert [message #645554 is a reply to message #645553]

Tue, 08 December 2015 11:59

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select * from v_det;
select * from v_det
              *
ERROR at line 1:
ORA-00942: table or view does not exist

As you can see we can't test any of our query to help you.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Help with insert [message #645555 is a reply to message #645553]

Tue, 08 December 2015 12:00

msol25
Messages: 396
Registered: June 2011

Senior Member

Dear,

I hope your query should be modify like this:


select s.sign_type
from   s_det s
where  s.curr_type = (         select    min(v.curr_type)
                               from      v_det v
                               where     nvl(v.curr_type,0) = nvl(s.curr_type,0)
                     )

Report message to a moderator