Home » SQL & PL/SQL » SQL & PL/SQL » SQl insert (10.2.0.1, Windows 2003)
SQl insert [message #354326] Fri, 17 October 2008 13:57 Go to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi all,

I am trying to script an insert query and need some help from you experts.

Below are two tables:

select * from form_items

case_cred        form_type        description

arc                    F                 A/E CC Auth
wri                    F                 Corr Inv Rel
ner                    M                 Back Order Rel
cct                    F                 Auto Order Rel
ikr                    F                  Inv Rel
klc                    F                 Aud In Table
sbn                    F                 Serv Inv Rel
chr                    M                 Cred Auth Rel


Select * From case_form 

case_name        case_seq        case_cred
test                  1                   arc
test                  2                   cct
test                  3                   klc        
test                  4                   ner
tech                  1                   chr
tech                  2                   ikr
file                  1                   wri
file                  2                   sbn
First inserting record into form_items table.


insert into form_items (case_cred,form_type,description) values [B]('are','F','A/R Auth Table')[/B]
1 row inserted.


Next, I need to insert a record into case_form table. The case_seq should be assigned based on the form_items.description in the alphabetical order. I can get the next case_seq from this below select query.


select a.description,a.case_cred,b.case_seq from form_items a,case_form b where
b.case_name='test' and a.case_cred=b.case_cred order by a.description

A/E CC Auth           arc         1
Auto Order Rel        cct         2
Aud In Table          klc         3
Back Order Rel        ner         4



For example, inserting below record in case_form,
insert into case_form (case_name,case_seq,case_cred) select 'test',[B]?[/B],'are' from case_form where case_name='test'; 

The form_items.description is 'A/R Auth Table' for case_cred='are'. So case_form.case_seq should be assigned '2'. But since cct has been assigned to 2 already, it has to be moved down to 3 and similarly other records in case_form. It should be like below after inserting this record in case_form.
Select * From case_form 

test                   1              arc
test                   2              are 
test                   3              cct                        
test                   4              klc        
test                   5              ner
tech                   1              chr
tech                   2              ikr
file                   1              wri
file                   2              sbn


Basically, we want to assign the case_seq based on the form_items description alphabetical order. We need a query to insert record into case_form table, that can assign the case_seq based on the description of the form_items table alphabetically for that corresponding case_cred. We have to script the query so we can give our clients to run it. Sorry this is very confusing, but please help me if you have any suggestions.

Thanks a lot.

[Updated on: Fri, 17 October 2008 14:06]

Report message to a moderator

Re: SQl insert [message #354399 is a reply to message #354326] Sat, 18 October 2008 07:09 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@sant_new,

I am not sure if you can achieve this through a single insert. Anyways I tried a few queries. Hope some of them will try to help you achieve your goal.


SQL*Plus: Release 11.1.0.6.0 - Production on Sat Oct 18 15:54:46 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter user-name: test_schema
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test_table
  2  (
  3  case_form varchar2(50),
  4  case_seq number,
  5  case_cred varchar2(50)
  6  );

Table created.

SQL> insert into test_table(case_form, case_seq, case_cred)
  2  values('TEST', '1', 'AAA');

1 row created.

SQL> insert into test_table(case_form, case_seq, case_cred)
  2  values('TEST', 2, 'ACC');

1 row created.

SQL> insert into test_table(case_form, case_seq, case_cred)
  2  values('TEST', 3, 'ADD');

1 row created.

SQL> insert into test_table(case_form, case_seq, case_cred)
  2  values('TECH', 1, 'BCD');

1 row created.

SQL> insert into test_table(case_form, case_seq, case_cred)
  2  values('TECH', 2, 'BEE');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_table;

CASE_FORM                 CASE_SEQ   CASE_CRED
------------------------- ---------- -------------------
TEST                               1  AAA
TEST                               2  ACC
TEST                               3  ADD
TECH                               1  BCD
TECH                               2  BEE

5 rows selected.

SQL> insert into test_table(case_form, case_seq, case_cred)
  2  values('TEST', null, 'ABC');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_table;

CASE_FORM                 CASE_SEQ   CASE_CRED
------------------------- ---------- -----------------
TEST                               1  AAA
TEST                               2  ACC
TEST                               3  ADD
TECH                               1  BCD
TECH                               2  BEE
TEST				      ABC

6 rows selected.

SQL> update test_table a
  2  set a.case_seq = (Select b1.case_seq from
  3  (select case_form,
  4     row_number() OVER (PARTITION BY case_form ORDER BY Case_Cred ASC) case_seq,
  5     case_cred from test_table) b1
  6    where b1.case_form = a.case_form and
  7      b1.case_cred = a.case_cred);

6 rows updated.

SQL> select * from test_table order by case_form, case_seq;

CASE_FORM                 CASE_SEQ   CASE_CRED
------------------------- ---------- --------------------
TECH                               1  BCD
TECH                               2  BEE
TEST                               1  AAA
TEST				   2  ABC
TEST                               3  ACC
TEST                               4  ADD

6 rows selected.


I am not an expert in Oracle, so I don't know if there are hidden traps of doing in such a way. One assumption I made in the above queries is that you won't be trying to insert any duplicate records.

Maybe others may point out some more once they go through the solution.

Hope this helps,

Regards,
Jo

[Updated on: Sat, 18 October 2008 07:13]

Report message to a moderator

Re: SQl insert [message #354462 is a reply to message #354326] Sun, 19 October 2008 07:39 Go to previous messageGo to next message
phani1980@gmail.com
Messages: 3
Registered: October 2008
Junior Member
Hi friend,
I guess you want into INSERT new value if it is not there in CASE_TABLE

Following the Insert you are using.

insert into case_form (case_name,case_seq,case_cred) select 'test',?,'are' from case_form where case_name='test';

You are hard-coding the 'are' value here which is not there in the SELECT statement.

If you are requirement is insert new value and when it taken new value get the new number for it. Then you create sequence and use it.

Syntax for creating the sequence.

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

what ever the value you give for the start with, nextval would get the another increment value.

insert into case_form (case_name,case_seq,case_cred) select 'test',sequence_name.nextval,'are' from case_form where case_name='test';

For the Advance Concepts of PL/SQL, I would suggest you go through the following URL:

http://alloracletech.blogspot.com/2008/08/plsql-advance.html

Re: SQl insert [message #354465 is a reply to message #354462] Sun, 19 October 2008 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Useless link to a blog, just spam.

Regards
Michel
Re: SQl insert [message #354474 is a reply to message #354462] Sun, 19 October 2008 09:44 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@phani1980@gmail.com,

Please read and understand OP's requirement before posting replies.

How will you update a part of the column values based on a current record insertion using sequence as per your reply?http://img2.mysmiley.net/imgs/smile/confused/confused0024.gif

Regards,
Jo

[Updated on: Sun, 19 October 2008 09:48]

Report message to a moderator

Re: SQl insert [message #354475 is a reply to message #354474] Sun, 19 October 2008 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
He does not want to understand OP's requirement or answer the question, he just wanted to post a link to his blog.

Regards
Michel
Re: SQl insert [message #354586 is a reply to message #354399] Mon, 20 October 2008 08:16 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Thanks for your reply, Jo. But all 3 fields in case_form table are not null. So I wont be able to insert null value and then update the case_seq.

Is there a way to get over this for not null fields?

Thank you
Re: SQl insert [message #354601 is a reply to message #354586] Mon, 20 October 2008 09:15 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@sant_new,

You can give a default value like 0(zero) for case_seq when you first insert a record and then later on update it.

Hope this helps.

Regards,
Jo

Re: SQl insert [message #354604 is a reply to message #354601] Mon, 20 October 2008 09:39 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
SQL> update test_table a
  2  set a.case_seq = (Select b1.case_seq from
  3  (select case_form,
  4     row_number() OVER (PARTITION BY case_form ORDER BY Case_Cred ASC) case_seq,
  5     case_cred from test_table) b1
  6    where b1.case_form = a.case_form and
  7      b1.case_cred = a.case_cred);


Jo, the update statement gets the case_seq number based on
ORDER BY Case_Cred ASC. But we need to generate the case_seq number based on the form_items.description values. Like below:

select a.description,a.case_cred,b.case_seq from form_items a,case_form b where b.case_name='test' and a.case_cred=b.case_cred order by a.description

A/E CC Auth           arc         1
Auto Order Rel        cct         2
Aud In Table          klc         3
Back Order Rel        ner         4


So, we need to get the case_form.case_seq generated from the form_items.description ASC.

Is it possible to update the case_seq based on this criteria??

Thank you very much for your help.
Re: SQl insert [message #354611 is a reply to message #354604] Mon, 20 October 2008 10:10 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@sant_new,

Yes it is possible (i guess). As I said in my first reply, the queries posted were mere example for you to achieve a solution for your problem.

If you are still unsuccessful in solving the problem then post the queries you have tried. I might be able to help based on the queries you have tried.

Good Luck...

Regards,
Jo
Re: SQl insert [message #354665 is a reply to message #354611] Mon, 20 October 2008 12:44 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Jo, I tried the queries below.


insert into form_items (case_cred,form_type,description) values [B]('are','F','A/R Auth Table')[/B]
1 row inserted.

select * from form_items

case_cred        form_type        description

arc                    F                 A/E CC Auth
wri                    F                 Corr Inv Rel
ner                    M                 Back Order Rel
cct                    F                 Auto Order Rel
ikr                    F                  Inv Rel
klc                    F                 Aud In Table
sbn                    F                 Serv Inv Rel
chr                    M                 Cred Auth Rel
are		       F		 A/R Auth Table



Select * From case_form 

case_name        case_seq        case_cred
test                  1                   arc
test                  2                   cct
test                  3                   klc        
test                  4                   ner
tech                  1                   chr
tech                  2                   ikr
file                  1                   wri
file                  2                   sbn

insert into case_form values [B]('test',0,'are')[/B]
1 row inserted.

update case_form 
  set case_seq = (select  row_number() OVER (PARTITION BY case_name ORDER BY b.description ASC) from case_form a, form_items b
  where a.case_name = 'test' and
  b.case_cred = a.case_cred);

ORA-01407: cannot update case_form.case_seq to NULL



I am trying to fit the below select query in the update statement
to get the case_seq based on the alphabetical order of form_items.description.

select a.description,a.case_cred,b.case_seq from form_items a,case_form b where b.case_name='test' and a.case_cred=b.case_cred order by a.description


I would appreciate any kind of help. Thanks a lott.





Re: SQl insert [message #354671 is a reply to message #354665] Mon, 20 October 2008 13:39 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@sant_new,
Quote:

update case_form
set case_seq = (select row_number() OVER (PARTITION BY case_name ORDER BY b.description ASC) from case_form a, form_items b
where a.case_name = 'test' and
b.case_cred = a.case_cred);


You have mentioned that case_seq cannot be set to null right?
Your above mentioned update statement tries to update all the records that means it tries to update the records not only with case_name as 'test' but the records with 'tech' and 'file' too. And the funny thing is it will try to update those records with null values since your subquery is restricting the case_seq values of those records by the following condition:
where a.case_name = 'test' 

So you are getting the error that:
ORA-01407: cannot update case_form.case_seq to NULL

Moreover, once you sort that out you will have to map the columns from your main table to the records of your subquery in such a way that for a particular record of the main query the sub query will have only one record. Just a pointer to avoid the following error:
SQL> UPDATE case_form c1
  2     SET case_seq =
  3            (SELECT ROW_NUMBER () OVER (PARTITION BY case_name ORDER BY b.description ASC)
  4               FROM case_form a, for_items b
  5              WHERE a.case_name = 'test' AND b.case_cred = a.case_cred);
          (SELECT ROW_NUMBER () OVER (PARTITION BY case_name ORDER BY b.description ASC)
           *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row


My apologies if I am confusing you. http://img2.mysmiley.net/imgs/smile/innocent/innocent0002.gif

Hope this helps.

Regards,
Jo

[Updated on: Mon, 20 October 2008 21:50]

Report message to a moderator

Previous Topic: Converting LONG to CLOB - ORACLE NOT CONECTED
Next Topic: nested materialized view
Goto Forum:
  


Current Time: Sat Dec 10 07:11:16 CST 2016

Total time taken to generate the page: 0.07480 seconds