CODEWITHSUNDEEP
db2-400
Cass
Cass

Reputation: 144

Selecting only a sequence

I would like to select in my table only a part after i read 00006 and stop selecting when i read the next pattern 00XXX .(here it's 00040 but it's could be an another number )

00006 
123456
456789
123789
00040
125478
547896
454623
519846
00001
....

Here , for example i would like to get only these values with a select :

123456 456789 123789

How is the way to obtain that ? I dont find any clue.

Thanks for your help.

ps: i have no rights for transform the table where i select

With the solution of Charles :

with marker as (
   select rownumber() over() as rowno
          , rrn(t) as recno, cast(substr(YYYYYY, 1, 5)as integer) as markvalue
   from fap1t010.£$ZZZZZZ t
   where substr(YYYYYY, 1, 5) like ('00___')
 ), dataa as (
   select rrn(t) as recno, cast(substr(YYYYYY, 1, 6)as integer) as datavalue
   from fap1t010.£$ZZZZZZ t
   where substr(YYYYYY, 1, 5) NOT like ('00006')
   and substr(YYYYYY, 13, 1) ='C'
), ranges as(
   select a.markvalue
         , a.recno as startrec
         , b.recno as endrec
   from marker A join marker B
     on b.rowno = a.rowno +1
)
 select distinct d.datavalue  
 from ranges R join dataa D
      on d.recno between r.startrec and r.endrec
 where r.markvalue = 00006

Upvotes: 1

Views: 53

Answers (1)

Charles
Charles

Reputation: 23783

Wow...that's an ugly request.

But it can be done:

with marker as (
   select rownumber() over() as rowno
          , rrn(t) as recno, myfld as markvalue
   from dtcwilt.temp t
   where myfld like ('00___ ')
 ), data as (
   select rrn(t) as recno, myfld as datavalue
   from dtcwilt.temp t
   where myfld NOT like ('00___ ')
), ranges as(
   select a.markvalue
         , a.recno as startrec
         , b.recno as endrec
   from marker A join marker B
     on b.rowno = a.rowno +1
)
 select d.datavalue  
 from ranges R join data D
      on d.recno between r.startrec and r.endrec
 where r.markvalue = '00006';

If you have a large data set, performance isn't going to be all that great; there's probably room for improvement. But the above is at least easy to understand.

If you happen to be on the latest version 7.3 of IBM i, you could probably simplify the statement by making use of the new LEAD() and/or LAG() functions. But I don't have 7.3 to test on.

Upvotes: 1

Related Questions