Home » SQL & PL/SQL » SQL & PL/SQL » Increment sequence value for set of rows (Oracle 9.2.0.3,XP)
Increment sequence value for set of rows [message #403680] Mon, 18 May 2009 01:03 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member

I have a table:



CREATE TABLE TEST ( ID NUMBER(2) NOT NULL,
                    EMPID NUMBER(3) NOT NULL,
                    DEPTID NUMBER(3) NOT NULL);

ID      EMPID     DEPTID

1	1	10	
1	2	10	
1	3	10
1	4	10
1	7	10



I have created a sequence in ID.
whenever a new deptid is inserted the ID values should be the next number


eg,

insert into test(empid,deptid) values(5,20);
insert into test(empid,deptid) values(6,20);
insert into test(empid,deptid) values(8,20);


ID      EMPID     DEPTID

1	1	10	
1	2	10	
1	3	10
1	4	10
1	7	10
2	3	20
2	4	20
2	7	20



Do I need to use statement level trigger for this before insert into table test?
Then, I want to pick the set of rows based on the existing random values of ID column

Thanks for help!

-prachi
Re: Increment sequence value for set of rows [message #403681 is a reply to message #403680] Mon, 18 May 2009 01:05 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Then, I want to pick the set of rows based on the existing random values of ID column
so proceed to do so.
What is preventing you from doing your goal?
Re: Increment sequence value for set of rows [message #403682 is a reply to message #403681] Mon, 18 May 2009 01:09 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Do I need to use statement level trigger for this before insert into table test?
Re: Increment sequence value for set of rows [message #403689 is a reply to message #403682] Mon, 18 May 2009 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: Increment sequence value for set of rows [message #403692 is a reply to message #403689] Mon, 18 May 2009 01:58 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thanks Michel sir,
But how can we assign new column value for the ID as new and old is not possible in statement level trigger.
Re: Increment sequence value for set of rows [message #403695 is a reply to message #403692] Mon, 18 May 2009 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't, you have to use a row level trigger.

Regards
Michel
Re: Increment sequence value for set of rows [message #403709 is a reply to message #403695] Mon, 18 May 2009 03:28 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I know how to insert next seq value in each row. But not understand how to insert for a group of rows.. where DEPTID is the key.Please help!
Re: Increment sequence value for set of rows [message #403710 is a reply to message #403709] Mon, 18 May 2009 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you want.
Post a full test case.

Regards
Michel
Re: Increment sequence value for set of rows [message #403711 is a reply to message #403710] Mon, 18 May 2009 04:00 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
CREATE TABLE TEST ( ID NUMBER(2) NOT NULL,
                    EMPID NUMBER(3) NOT NULL,
                    DEPTID NUMBER(3) NOT NULL);
					
					
					
CREATE SEQUENCE SQ_ID 
START WITH 1
INCREMENT BY 1
NOCACHE
NO CYCLE;


When I execute the below DML,

					
insert into test(empid,deptid) values(1,10);
insert into test(empid,deptid) values(2,10);
insert into test(empid,deptid) values(3,10);


The sequence value 1 should be inserted into ID for all the above three rows
ID      EMPID     DEPTID

1		1		10	
1		2		10	
1		3		10

Now when we insert with different dept id, the seq value 2 should be inserted
eg,
insert into test(empid,deptid) values(5,20);
insert into test(empid,deptid) values(6,20);
insert into test(empid,deptid) values(8,20);

after this I should get,

select * from test;

ID      EMPID     DEPTID

1		1		10	
1		2		10	
1		3		10
2		5		20
2		6		20
2		8		20

I tried a row level trigger like below but not being able to get what i want
CREATE OR REPLACE TRIGGER tr_test1
BEFORE INSERT
ON test
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN 
IF :NEW.deptid <> :OLD.deptid THEN

  SELECT SQ_ID.nextval
  INTO :new.ID
  FROM dual;

 end if;
 END;
/

[Updated on: Mon, 18 May 2009 04:01]

Report message to a moderator

Re: Increment sequence value for set of rows [message #403712 is a reply to message #403711] Mon, 18 May 2009 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And happens if you insert a next set of rows in dept 10?
(Until now, you have id=deptid/10.)

Regards
Michel
Re: Increment sequence value for set of rows [message #403713 is a reply to message #403712] Mon, 18 May 2009 04:07 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I didn't understand you Sir.
The test case I have put is a dummy one..The dept id may be 5 digit number like 12345 , 56781 etc.
So cant do dept id/10

for all the entries for a particular dept id it ID should be same (say 1 for all possible combination of rows)

[Updated on: Mon, 18 May 2009 04:14]

Report message to a moderator

Re: Increment sequence value for set of rows [message #403715 is a reply to message #403713] Mon, 18 May 2009 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you mean that all rows from a deptid have the same id?
So why is it not just deptid?

Regards
Michel
Re: Increment sequence value for set of rows [message #403718 is a reply to message #403715] Mon, 18 May 2009 05:20 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Do you mean that all rows from a deptid have the same id?
-Yes

dept id can be a 5/6 digit number
like 123457 234568


for 123457 Id is always 1 (say)
234568 ---Id is 3 (say)
Re: Increment sequence value for set of rows [message #403724 is a reply to message #403718] Mon, 18 May 2009 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
dept id can be a 5/6 digit number

Not with the definition of the table you gave.

Quote:
for 123457 Id is always 1 (say)
234568 ---Id is 3 (say)

Why do you need ID? If ID is always the same for a DEPTID and vice versa, then there is no reason to have 2 values ID and DEPTID.

Regards
Michel

[Updated on: Mon, 18 May 2009 05:45]

Report message to a moderator

Re: Increment sequence value for set of rows [message #403730 is a reply to message #403724] Mon, 18 May 2009 06:20 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
yes correct. Thats why I replied in my previous post that its a dummy one...
a set of combination of rows will be inserted in the table with same dept id...

seq no. is being used to keep track of it and also to select the random selection of data


[Updated on: Mon, 18 May 2009 06:30]

Report message to a moderator

Re: Increment sequence value for set of rows [message #403733 is a reply to message #403730] Mon, 18 May 2009 06:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That makes no sense - any criteria that you use with the ID to select a random sample of data could just be used with DEPT_ID instead.

The column ID has a 1-1 mapping with DEPTID, and is completely unnecessary.

If you really really want to add this sequence, just add it to the DEPT table instead - it will be much easier to add there, and will provide exactly the same level of functionality.
Re: Increment sequence value for set of rows [message #403737 is a reply to message #403733] Mon, 18 May 2009 06:57 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thanks Jrowbottom,
my requirement is
ID             EMPID             DEPTID

1		1		11543	
1		2		11543	
1		3		11543
2		5		20765
2		6		20765
2		8		20765


In the real scenario its not the department id..
I want to create a trigger that would enter sequence values for
different combinations of group of rows agains deptid.

Updated:Format

[Updated on: Mon, 18 May 2009 06:58]

Report message to a moderator

Re: Increment sequence value for set of rows [message #403738 is a reply to message #403737] Mon, 18 May 2009 07:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So what is the real scenario? What are these sets/groups of rows? What is the need of this sequence?

Regards
Michel
Re: Increment sequence value for set of rows [message #403739 is a reply to message #403737] Mon, 18 May 2009 07:08 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Your example still shows a 1 to 1 relationship between Dept_id and ID, therefore the answers that you have recieved so far remain pertinent.
Re: Increment sequence value for set of rows [message #403740 is a reply to message #403738] Mon, 18 May 2009 07:16 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
ID DEPTID BRANCH
1   10      1
1   10      2
1   10      3 
2   15      1
2   15      2  


these there combinations are unique
and Id will be used further to pick random combination of rows.
when fetched onse set of rows will be based on random existing ID values

1 10 1
1 10 2
1 10 3

or,

2 15 1
2 15 2
Re: Increment sequence value for set of rows [message #403742 is a reply to message #403740] Mon, 18 May 2009 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is the difference with random deptid?

Regards
Michel
Re: Increment sequence value for set of rows [message #403743 is a reply to message #403740] Mon, 18 May 2009 07:21 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
still a 1 to 1 relationship between id and deptid. Previous answers remain.
Re: Increment sequence value for set of rows [message #403773 is a reply to message #403740] Mon, 18 May 2009 10:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What you're looking for, as well as being pointless, is also going to be quite difficult to code - ask yourself how you're going to make it work in a multi-user environment?

If I had a sudden rush of blood to the head, and actually decided this was the best solution to a problem I had, then I'd probably create a view on that table, and use Dense_Rank() over (Order by Deptid) and populate the column on the fly.
Re: Increment sequence value for set of rows [message #403775 is a reply to message #403680] Mon, 18 May 2009 10:12 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>seq no. is being used to keep track of it and also to select the random selection of data
Why must the data be random?
How will the code act any differently if/when the data is "not random"?
How will the code determine if the data is "random" or not?
Re: Increment sequence value for set of rows [message #430810 is a reply to message #403775] Thu, 12 November 2009 06:44 Go to previous messageGo to next message
biping
Messages: 1
Registered: November 2009
Location: Mumbai
Junior Member
Here you go,

Remeber if you are inserting separate inserts you will have different IDs.

However if is one statement of inserting multiple rows then you will have same ID.

Create a package pkg_gv with global variable ids

create or replace trigger tr_b4i_stmt before insert on test
begin
select sq_id.nextval into pkg_gv.ids from dual;
end;
/

Trigger created.

CREATE OR REPLACE TRIGGER tr_test1
BEFORE INSERT
ON test for each row
begin
:new.id := pkg_gv.ids;
end;
/

Trigger created.

insert into test(empid,deptid) values(1,10);

1 record created.

insert into test(empid,deptid) values(2,10);

1 record created.

insert into test(empid,deptid) values(3,10);

1 record created.

select * from test;

ID EMPID DEPTID
----- ---------- ----------
1 1 10
2 2 10
3 3 10

insert into test(empid,deptid) select empid,deptid from test;

3 Rows inserted

select * from test;

ID EMPID DEPTID
----- ---------- ----------
1 1 10
2 2 10
3 3 10
4 1 10
4 2 10
4 3 10

[Updated on: Thu, 12 November 2009 06:48]

Report message to a moderator

Re: Increment sequence value for set of rows [message #430813 is a reply to message #430810] Thu, 12 November 2009 06:51 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
1. Old post.
2. Doesn't perform what the OP asked for.
3. What the OP asked for was pointless anyway.
Re: Increment sequence value for set of rows [message #430821 is a reply to message #430813] Thu, 12 November 2009 07:21 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
4. Is not formated as required in OraFAQ Forum Guide, "How to format your post?" section.

Regards
Michel
Previous Topic: Not a valid month
Next Topic: Convert rows to cols with distinct, sys_connect_by_path
Goto Forum:
  


Current Time: Sat Dec 03 08:17:32 CST 2016

Total time taken to generate the page: 0.12431 seconds