Home » SQL & PL/SQL » SQL & PL/SQL » Re-Arrange Serial (Oracle 10G, Windows 2003)  () 1 Vote
Re-Arrange Serial [message #575202] Mon, 21 January 2013 04:59 Go to next message
mmohsinaziz
Messages: 88
Registered: May 2012
Member
Dear all,
I want to conditionally re-arrange the serial.
Sample test case is as below.

DROP TABLE t1;
CREATE TABLE t1
(
  NO     NUMBER(5)                              NOT NULL,
  TYPE   VARCHAR2(1)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

ALTER TABLE t1 ADD (
  CONSTRAINT PK_T1_NO
 PRIMARY KEY
 (NO));

 INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1245, 'R'
               );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1246, 'N'
             );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1247, 'N'
               );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1248, 'R'
               );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1249, 'R'
               );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1250, 'R'
               );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1251, 'N'
               );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1252, 'N'
                );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1253, 'R'
                );
  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1254, 'R'
                );
  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1255, 'N'
                );

COMMIT;



SQL> Select * from t1 order by no;

        NO T
---------- -
      1245 R
      1246 N
      1247 N
      1248 R
      1249 R
      1250 R
      1251 N
      1252 N
      1253 R
      1254 R
      1255 N

11 rows selected.

Required output is

    OLD_NO T  NEW_NO
---------- - -------- 
      1245 R   1245
      1246 N   1247
      1247 N   1251
      1248 R   1248
      1249 R   1249
      1250 R   1250
      1251 N   1252
      1252 N   1255
      1253 R   1253
      1254 R   1254
      1255 N   1256


Scnario is to add 1 in no. field but no.s having type 'R' will remain unchanged. e.g. Record no. 1247 is changed to 1251 because 1248,1249,1250 have typ 'R'.

Regards
Muhammad Mohsin
Re: Re-Arrange Serial [message #575204 is a reply to message #575202] Mon, 21 January 2013 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How is this different from your previous topic?

http://www.orafaq.com/forum/m/574816/102589/#msg_574816

Regards
Michel
Re: Re-Arrange Serial [message #575208 is a reply to message #575204] Mon, 21 January 2013 05:19 Go to previous messageGo to next message
mmohsinaziz
Messages: 88
Registered: May 2012
Member
Dear Michel,
Yes, it is the continuity of my previous topic but in a different way. I have tried to simplify the requirement. So that i can get the help from you guys.

Regards
Muhammad Mohsin
Re: Re-Arrange Serial [message #575209 is a reply to message #575208] Mon, 21 January 2013 05:22 Go to previous messageGo to next message
mmohsinaziz
Messages: 88
Registered: May 2012
Member
I have tried the following query.

SELECT   NO old_no, TYPE,
         CASE
            WHEN LEAD (TYPE) OVER (ORDER BY NO) = 'R'
               THEN NO
            ELSE NO + 1
         END new_no
    FROM t1
   WHERE TYPE = 'N'
ORDER BY NO

The output is
OLD_NO T NEW_NO
---------- - ----------
1246 N 1247
1247 N 1248
1251 N 1252
1252 N 1253
1255 N 1256

Please help me to solve it.

Regards
Muhammad Mohsin
Re: Re-Arrange Serial [message #575247 is a reply to message #575209] Mon, 21 January 2013 13:13 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
mmohsinaziz wrote on Mon, 21 January 2013 06:22
I have tried the following query.


You we going the right direction:

select  no old_no,
        type,
        case type
          when 'R' then no
          else nvl(lead(case type when 'N' then no end ignore nulls) over(order by no),no + 1)
        end new_no
  from  t1
  order by no
/

    OLD_NO T     NEW_NO
---------- - ----------
      1245 R       1245
      1246 N       1247
      1247 N       1251
      1248 R       1248
      1249 R       1249
      1250 R       1250
      1251 N       1252
      1252 N       1255
      1253 R       1253
      1254 R       1254
      1255 N       1256

11 rows selected.

SQL>


SY.
Re: Re-Arrange Serial [message #575284 is a reply to message #575247] Mon, 21 January 2013 22:23 Go to previous messageGo to next message
mmohsinaziz
Messages: 88
Registered: May 2012
Member
Thanks Solomon.
Re: Re-Arrange Serial [message #575287 is a reply to message #575284] Mon, 21 January 2013 22:47 Go to previous messageGo to next message
mmohsinaziz
Messages: 88
Registered: May 2012
Member
Dear Solomon,

It is giving me the following error.

test@orcl>select  no old_no,
  2          type,
  3          case type
  4            when 'R' then no
  5            else nvl(lead(case type when 'N' then no end ignore nulls) over(order by no),no + 1)
  6          end new_no
  7    from  t1
  8    order by no
  9  /
          else nvl(lead(case type when 'N' then no end ignore nulls) over(order by no),no + 1)
                                                       *
ERROR at line 5:
ORA-00907: missing right parenthesis
Re: Re-Arrange Serial [message #575289 is a reply to message #575287] Tue, 22 January 2013 00:10 Go to previous messageGo to next message
mmohsinaziz
Messages: 88
Registered: May 2012
Member
The result Solomon query is per requirement but I think ignore nulls is not supported in 10G. Please suggest the alternate of ignore nulls.

I have also tried the following query. It is giving the same result as if we donot use ignore nulls in Solomon's query.

select no old_no
   , type
--   , max(no) over (order by grp range between 1 following and 1 following) new_no
   , case type
           when 'R' then no
           else nvl(max(no) over (order by grp range between 1 following and 1 following),no+1) end new
from (select no
   ,type
   , sum(nvl2(no,1,0)) over (order by no) grp
   from t1
)
order by no
/

    OLD_NO T        NEW
---------- - ----------
      1245 R       1245
      1246 N       1247
      1247 N       1248
      1248 R       1248
      1249 R       1249
      1250 R       1250
      1251 N       1252
      1252 N       1253
      1253 R       1253
      1254 R       1254
      1255 N       1256

11 rows selected.



Result is not as per requirement. Please help to resolve it.

Regards
M. Mohsin
Re: Re-Arrange Serial [message #575330 is a reply to message #575289] Tue, 22 January 2013 05:13 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
mmohsinaziz wrote on Tue, 22 January 2013 01:10
The result Solomon query is per requirement but I think ignore nulls is not supported in 10G.


This will teach you to post version:

select  no old_no,
        type,
        case type
          when 'R' then no
          else nvl(lead(case type when 'N' then no end) over(order by case type when 'N' then no end nulls last),no + 1)
        end new_no
  from  t1
  order by no
/

    OLD_NO T     NEW_NO
---------- - ----------
      1245 R       1245
      1246 N       1247
      1247 N       1251
      1248 R       1248
      1249 R       1249
      1250 R       1250
      1251 N       1252
      1252 N       1255
      1253 R       1253
      1254 R       1254
      1255 N       1256

11 rows selected.

SQL>


SY.
Re: Re-Arrange Serial [message #575401 is a reply to message #575330] Tue, 22 January 2013 22:36 Go to previous messageGo to next message
mmohsinaziz
Messages: 88
Registered: May 2012
Member
Thanks. Solomon
It works for me.

Regards
Muhammad Mohsin
Re: Re-Arrange Serial [message #575917 is a reply to message #575401] Tue, 29 January 2013 03:01 Go to previous messageGo to next message
mmohsinaziz
Messages: 88
Registered: May 2012
Member
Dear Solomon,
I have observed the following problem in this query.
When the last record is of type 'R' then previous 'N' Type record sequence is not generated properly.

DROP TABLE t1;
CREATE TABLE t1
(
  NO     NUMBER(5)                              NOT NULL,
  TYPE   VARCHAR2(1)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

ALTER TABLE t1 ADD (
  CONSTRAINT PK_T1_NO
 PRIMARY KEY
 (NO));

 INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1245, 'R'
               );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1246, 'N'
             );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1247, 'N'
               );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1248, 'R'
               );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1249, 'R'
               );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1250, 'R'
               );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1251, 'N'
               );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1252, 'N'
                );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1253, 'R'
                );
  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1254, 'R'
                );
  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1255, 'N'
                );

  INSERT INTO T1
                (NO, TYPE
                )
         VALUES (1256, 'R'
                );
commit;


SQL>select * from t1 order by no;

        NO T
---------- -
      1245 R
      1246 N
      1247 N
      1248 R
      1249 R
      1250 R
      1251 N
      1252 N
      1253 R
      1254 R
      1255 N
      1256 R
12 rows selected.


Query Output is
SQL>select  no old_no,
           type,
           case type
             when 'R' then no
            else nvl(lead(case type when 'N' then no end) over(order by case type when 'N' then no end nulls last),no + 1)
            end new_no
      from  t1
      order by no
    /

    OLD_NO T     NEW_NO
---------- - ----------
      1245 R       1245
      1246 N       1247
      1247 N       1251
      1248 R       1248
      1249 R       1249
      1250 R       1250
      1251 N       1252
      1252 N       1255
      1253 R       1253
      1254 R       1254
      1255 N       1256
      1256 R       1256

12 rows selected.



New Record 1256 is duplicate. It must be 1257. Please help to resovle it also.

Required Output is
    OLD_NO T     NEW_NO
---------- - ----------
      1245 R       1245
      1246 N       1247
      1247 N       1251
      1248 R       1248
      1249 R       1249
      1250 R       1250
      1251 N       1252
      1252 N       1255
      1253 R       1253
      1254 R       1254
      1255 N       1257
      1256 R       1256

12 rows selected.


Thanks
M. Mohsin
Re: Re-Arrange Serial [message #575925 is a reply to message #575917] Tue, 29 January 2013 06:38 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
mmohsinaziz wrote on Tue, 29 January 2013 04:01
I have observed the following problem in this query.


You're right. When there are no leading type N no's we should use max(no) + 1, not no + 1:

select  no old_no,
           type,
           case type
             when 'R' then no
            else nvl(
                     lead(case type when 'N' then no end)
                       over(order by case type when 'N' then no end nulls last),
                     max(no) over() + 1
                    )
            end new_no
      from  t1
      order by no
/

    OLD_NO T     NEW_NO
---------- - ----------
      1245 R       1245
      1246 N       1247
      1247 N       1251
      1248 R       1248
      1249 R       1249
      1250 R       1250
      1251 N       1252
      1252 N       1255
      1253 R       1253
      1254 R       1254
      1255 N       1257

    OLD_NO T     NEW_NO
---------- - ----------
      1256 R       1256

12 rows selected.

SQL>  


SY.
Re: Re-Arrange Serial [message #575978 is a reply to message #575925] Wed, 30 January 2013 01:40 Go to previous message
mmohsinaziz
Messages: 88
Registered: May 2012
Member
Thanks
Previous Topic: Diffderence between count(*) , count(1)
Next Topic: accessing xml values from a table
Goto Forum:
  


Current Time: Thu Oct 02 12:35:07 CDT 2014

Total time taken to generate the page: 0.11353 seconds