OraFAQ Forum: SQL & PL/SQL » Create Gap in Series

Home » SQL & PL/SQL » SQL & PL/SQL » Create Gap in Series (Oracle 10G, Windows 2003)

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

Create Gap in Series [message #574816]

Wed, 16 January 2013 00:33

mmohsinaziz
Messages: 79
Registered: May 2012

Member

Dears Friends,
I need to find a query to create a gap in continious series. So that i can fill this gap with a new record.

Attached here is the test sql.

Sample Data is

test@orcl>select * from sales_mst;

        NO VDATE     T
---------- --------- -
      1245 07-JAN-13 N
      1246 07-JAN-13 N
      1247 07-JAN-13 R
      1248 07-JAN-13 N
      1249 08-JAN-13 N
      1250 08-JAN-13 R
      1251 08-JAN-13 N
      1252 09-JAN-13 R
      1253 09-JAN-13 N
      1254 09-JAN-13 N

10 rows selected.

test@orcl>select * from sales_dtl;

        NO       CODE        QTY       RATE
---------- ---------- ---------- ----------
      1245         11         60        600
      1246         12         55        450
      1246         11         45        600
      1247         13         50        250
      1247         11         60        600
      1248         11         45        600
      1249         12         55        450
      1249         11         45        600
      1250         18         41        250
      1250         23         60        521
      1251         27         65        312
      1252         18         41        250
      1252         23         60        521
      1253         37         66        310
      1254         32         95        112
      1254         39        105        541

16 rows selected.

Required Out Put

test@orcl>select * from sales_mst;

        NO VDATE     T
---------- --------- -
      1245 07-JAN-13 N
      1246 07-JAN-13 N
      1247 07-JAN-13 R
      1248 07-JAN-13 N
      1251 08-JAN-13 N
      1250 08-JAN-13 R
      1253 08-JAN-13 N
      1252 09-JAN-13 R
      1254 09-JAN-13 N
      1255 09-JAN-13 N

10 rows selected.

test@orcl>select * from sales_dtl;

        NO       CODE        QTY       RATE
---------- ---------- ---------- ----------
      1245         11         60        600
      1246         12         55        450
      1246         11         45        600
      1247         13         50        250
      1247         11         60        600
      1248         11         45        600
      1251         12         55        450
      1251         11         45        600
      1250         18         41        250
      1250         23         60        521
      1253         27         65        312
      1252         18         41        250
      1252         23         60        521
      1254         37         66        310
      1255         32         95        112
      1255         39        105        541

16 rows selected.

Update or move only those records which have Type 'N'.
I have tried the following query but it is giving me an error.

test@orcl>ALTER TABLE
   sales_dtl
DISABLE CONSTRAINT
   fk_sales_dtl_no;

test@orcl>update sales_mst set no=no+1 where type='N' and vdate > '07-JAN-13';

*
ERROR at line 1:
ORA-00001: unique constraint (TEST.PK_SALES_MST_NO) violated

Regards
Muhammad Mohsin

Attachment: test.sql
(Size: 3.89KB, Downloaded 11 times)

Report message to a moderator

Re: Create Gap in Series [message #574818 is a reply to message #574816]

Wed, 16 January 2013 00:38

delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City

Senior Member

Check the error message, it clearly says which constraint gets violated.

regards,
Delna

[Updated on: Wed, 16 January 2013 00:38]

Report message to a moderator

Re: Create Gap in Series [message #574824 is a reply to message #574816]

Wed, 16 January 2013 00:50

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table 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.

Note that '07-JAN-13' is NOT a date but a string, the proof:

SQL> select to_date('07-JAN-13') from dual;
select to_date('07-JAN-13') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel

Report message to a moderator

Re: Create Gap in Series [message #574827 is a reply to message #574818]

Wed, 16 January 2013 00:59

mmohsinaziz
Messages: 79
Registered: May 2012

Member

Yes, it clearly says unique constraints gets violated. Thats why i am looking for a query to update only those records which have type 'N' and if the next no is having the type 'R' it skip that no. and start updating the no. from next no. which have type 'N'

regards
M. Mohsin

Report message to a moderator

Re: Create Gap in Series [message #574829 is a reply to message #574827]

Wed, 16 January 2013 01:02

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What we are looking for is a test case.
Without it we can't help.

Regards
Michel

Report message to a moderator

Re: Create Gap in Series [message #574832 is a reply to message #574827]

Wed, 16 January 2013 01:07

mmohsinaziz
Messages: 79
Registered: May 2012

Member

Dear Michel,
Sorry, I will take care.

test@orcl>ALTER TABLE
   sales_dtl
DISABLE CONSTRAINT
   fk_sales_dtl_no;

test@orcl>update sales_mst set no=no+1 where type='N' and vdate > to_date('07-JAN-13','dd-mon-rr');

*
ERROR at line 1:
ORA-00001: unique constraint (TEST.PK_SALES_MST_NO) violated

Regards
M. Mohsin

Report message to a moderator

Re: Create Gap in Series [message #574843 is a reply to message #574832]

Wed, 16 January 2013 01:29

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So change what you do to avoid this error.

Regards
Michel

Report message to a moderator

Re: Create Gap in Series [message #574844 is a reply to message #574832]

Wed, 16 January 2013 01:46

mmohsinaziz
Messages: 79
Registered: May 2012

Member

Dear Michel,
Following is sql script

CREATE TABLE SALES_MST
(
  NO     NUMBER(5)                              NOT NULL,
  VDATE  DATE,
  TYPE   VARCHAR2(1)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;


ALTER TABLE SALES_MST ADD (
  CONSTRAINT PK_SALES_MST_NO
 PRIMARY KEY
 (NO));

CREATE TABLE SALES_DTL
(
  NO    NUMBER(4),
  CODE  NUMBER(2),
  QTY   NUMBER(3),
  RATE  NUMBER(5,2)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;


ALTER TABLE SALES_DTL ADD (
  CONSTRAINT FK_SALES_DTL_NO 
 FOREIGN KEY (NO) 
 REFERENCES SALES_MST (NO));

INSERT INTO SALES_MST
            (NO, vdate, TYPE
            )
     VALUES (1245, TO_DATE ('07-jan-13', 'dd-mon-rr'), 'N'
            );

INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1245, 11, 60, 600
            );

INSERT INTO SALES_MST
            (NO, vdate, TYPE
            )
     VALUES (1246, TO_DATE ('07-jan-13', 'dd-mon-rr'), 'N'
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1246, 12, 55, 450
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1246, 11, 45, 600
            );

INSERT INTO SALES_MST
            (NO, vdate, TYPE
            )
     VALUES (1247, TO_DATE ('07-jan-13', 'dd-mon-rr'), 'R'
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1247, 13, 50, 250
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1247, 11, 60, 600
            );

INSERT INTO SALES_MST
            (NO, vdate, TYPE
            )
     VALUES (1248, TO_DATE ('07-jan-13', 'dd-mon-rr'), 'N'
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1248, 11, 45, 600
            );


INSERT INTO SALES_MST
            (NO, vdate, TYPE
            )
     VALUES (1249, TO_DATE ('08-jan-13', 'dd-mon-rr'), 'N'
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1249, 12, 55, 450
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1249, 11, 45, 600
            );

INSERT INTO SALES_MST
            (NO, vdate, TYPE
            )
     VALUES (1250, TO_DATE ('08-jan-13', 'dd-mon-rr'), 'R'
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1250, 18, 41, 250
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1250, 23, 60, 521
            );

INSERT INTO SALES_MST
            (NO, vdate, TYPE
            )
     VALUES (1251, TO_DATE ('08-jan-13', 'dd-mon-rr'), 'N'
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1251, 27, 65, 312
            );

INSERT INTO SALES_MST
            (NO, vdate, TYPE
            )
     VALUES (1252, TO_DATE ('09-jan-13', 'dd-mon-rr'), 'R'
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1252, 18, 41, 250
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1252, 23, 60, 521
            );

INSERT INTO SALES_MST
            (NO, vdate, TYPE
            )
     VALUES (1253, TO_DATE ('09-jan-13', 'dd-mon-rr'), 'N'
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1253, 37, 66, 310
            );
INSERT INTO SALES_MST
            (NO, vdate, TYPE
            )
     VALUES (1254, TO_DATE ('09-jan-13', 'dd-mon-rr'), 'N'
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1254, 32, 95, 112
            );
INSERT INTO SALES_DTL
            (NO, code, qty, rate
            )
     VALUES (1254, 39, 105, 541
            );

Requirement is
1) Create a gaps in serail to add more records to fill that gaps.
2) Serail no. of those records having Type 'R' will not be changed.

In sample output record no 1249 is updated as 1251 and 1251 is updated as 1253 because 1250,1252 have a type 'R'.

        NO VDATE     T  OLD_NO
---------- --------- -  -------
      1245 07-JAN-13 N   1245
      1246 07-JAN-13 N   1246
      1247 07-JAN-13 R   1247
      1248 07-JAN-13 N   1248
      1251 08-JAN-13 N   1249
      1250 08-JAN-13 R   1250
      1253 08-JAN-13 N   1251
      1252 09-JAN-13 R   1252
      1254 09-JAN-13 N   1253
      1255 09-JAN-13 N   1254

Hopefully it is clear now.

Regards
M. Mohsin

Report message to a moderator

Re: Create Gap in Series [message #574845 is a reply to message #574844]

Wed, 16 January 2013 01:50

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

As you cannot update 2 tables at the same time, you have to declare your constraints as "DEFERRABLE INITIALLY DEFERRED.

Regards
Michel

Report message to a moderator

Re: Create Gap in Series [message #574846 is a reply to message #574845]

Wed, 16 January 2013 01:53

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Actually rereading your requirement I don't understand it.
Why 1249 is update to 1251? How do you find 1251?

Regards
Michel

Report message to a moderator

Re: Create Gap in Series [message #574848 is a reply to message #574846]

Wed, 16 January 2013 01:58

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select * from SALES_MST order by 1;
        NO VDATE       T
---------- ----------- -
      1245 07-JAN-2013 N
      1246 07-JAN-2013 N
      1247 07-JAN-2013 R
      1248 07-JAN-2013 N
      1249 08-JAN-2013 N
      1250 08-JAN-2013 R
      1251 08-JAN-2013 N
      1252 09-JAN-2013 R
      1253 09-JAN-2013 N
      1254 09-JAN-2013 N

10 rows selected.

SQL> select * from  SALES_DTL order by 1;
        NO       CODE        QTY       RATE
---------- ---------- ---------- ----------
      1245         11         60        600
      1246         12         55        450
      1246         11         45        600
      1247         13         50        250
      1247         11         60        600
      1248         11         45        600
      1249         12         55        450
      1249         11         45        600
      1250         18         41        250
      1250         23         60        521
      1251         27         65        312
      1252         18         41        250
      1252         23         60        521
      1253         37         66        310
      1254         32         95        112
      1254         39        105        541

16 rows selected.

Why 1246 is not updated to 1248?

When you say 1249 is updated to 1251, you mean all rows of SALES_DTL with no=1249?
Please post the final result you want.

Regards
Michel

Report message to a moderator

Re: Create Gap in Series [message #574849 is a reply to message #574848]

Wed, 16 January 2013 02:08

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Is this what you want:

SQL> select * from  SALES_DTL order by 1;
        NO       CODE        QTY       RATE
---------- ---------- ---------- ----------
      1245         11         60        600
      1246         12         55        450
      1246         11         45        600
      1247         13         50        250
      1247         11         60        600
      1248         11         45        600
      1249         12         55        450
      1249         11         45        600
      1250         18         41        250
      1250         23         60        521
      1251         27         65        312
      1252         18         41        250
      1252         23         60        521
      1253         37         66        310
      1254         32         95        112
      1254         39        105        541

16 rows selected.

SQL> update SALES_DTL dtl
  2  set no = (select min(decode(type,'R',null,no))
  3            from SALES_MST mst
  4            where mst.no > dtl.no)
  5  where 'R' = (select type from sales_mst mst where mst.no = dtl.no+1)
  6  /

5 rows updated.

SQL> select * from  SALES_DTL order by 1;
        NO       CODE        QTY       RATE
---------- ---------- ---------- ----------
      1245         11         60        600
      1247         13         50        250
      1247         11         60        600
      1248         12         55        450
      1248         11         45        600
      1248         11         45        600
      1250         18         41        250
      1250         23         60        521
      1251         12         55        450
      1251         11         45        600
      1252         18         41        250
      1252         23         60        521
      1253         27         65        312
      1253         37         66        310
      1254         32         95        112
      1254         39        105        541

16 rows selected.

Regards
Michel

Report message to a moderator

Re: Create Gap in Series [message #574860 is a reply to message #574849]

Wed, 16 January 2013 03:36

mmohsinaziz
Messages: 79
Registered: May 2012

Member

Dear Michel,
Thanks for your reply.

Let me try to explain the things clearly. Obvesiouly then you will be able to help me.

1) We want to create gap right after the date 07-jan-13.
The first no. after 07-jan-13 is 1249. To create a gap we have to move 1249 to 1250 But 1250's type is 'R' and we can not change it. So we have to move the 1249 to 1251.
Data no. 1251 to 1252 but data no. 1252 has type 'R' so we have to move 1251 to 1253.

First it will be done in sales_mst (It is master table).
Sales_dtl is the detail table of sales_mst then we have to move the detail entries of 1249 to 1251.
The same will be applied for the onward records of master and detail tables.

2) Sample output will be

test@orcl>select * from sales_mst;

        NO VDATE     T
---------- --------- -
      1245 07-JAN-13 N
      1246 07-JAN-13 N
      1247 07-JAN-13 R
      1248 07-JAN-13 N
      1251 08-JAN-13 N
      1250 08-JAN-13 R
      1253 08-JAN-13 N
      1252 09-JAN-13 R
      1254 09-JAN-13 N
      1255 09-JAN-13 N

10 rows selected.

test@orcl>select * from sales_dtl;

        NO       CODE        QTY       RATE
---------- ---------- ---------- ----------
      1245         11         60        600
      1246         12         55        450
      1246         11         45        600
      1247         13         50        250
      1247         11         60        600
      1248         11         45        600
      1251         12         55        450
      1251         11         45        600
      1250         18         41        250
      1250         23         60        521
      1253         27         65        312
      1252         18         41        250
      1252         23         60        521
      1254         37         66        310
      1255         32         95        112
      1255         39        105        541

16 rows selected.

Regards
M. Mohsin

Report message to a moderator

Re: Create Gap in Series [message #574881 is a reply to message #574860]

Wed, 16 January 2013 08:42

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Wed, 16 January 2013 09:08

Is this what you want:

If not, explain why.

Regards
Michel

Report message to a moderator

Re: Create Gap in Series [message #574940 is a reply to message #574881]

Wed, 16 January 2013 23:38

mmohsinaziz
Messages: 79
Registered: May 2012

Member

Dear Michel,
This is not that i am looking for because
In this example we are only updating the sales_dtl table while i require to update sales_mst first then sales_dtl.

Sales_dtl table is also not updated correctly. data no. 1251 and 1253 are merged in 1253. Correct should be 1251 will move to 1253 and 1253 to 1254 and 1254 to 1255.

Regards
M. Mohsin

Report message to a moderator

Re: Create Gap in Series [message #574948 is a reply to message #574940]

Thu, 17 January 2013 00:51

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You did NEVER talk about an update of sales_dtl.
Now I don't understand your NEW requirements.
And I'm tired of this topic, so I give up.

Regards
Michel

Report message to a moderator

Previous Topic:	Total Recors Count Calculation
Next Topic:	Joining Tables and getting the record with the least/min date value

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 21:06:24 CDT 2013

Total time taken to generate the page: 0.28012 seconds