Re: Range of values by item

From: Rob van Wijk <rwijk72_at_gmail.com>
Date: Tue, 03 Jul 2007 08:21:53 -0000
Message-ID: <1183450913.770290.269760@c77g2000hse.googlegroups.com>

First of all, it's very good to post create table script, insert statements and the expected output.
This guarantees more people are willing to look into your problem.

However, when running your scripts, first I got a ".ORA-01401: inserted value too large for column".
When expanding the "Reader" column to a VARCHAR2(10), the table was populated, but your query returned:

STA END READER
--- --- ----------

1   1   KFVAN_KIRK
10  10  KFVAN_KIRK
100 109 KFVAN_KIRK

101 190 JWCASHEN
11 11 KFVAN_KIRK
11 19 MWLOVEJOY

110 119 KFVAN_KIRK
12  12  KFVAN_KIRK
120 129 KFVAN_KIRK
13  13  KFVAN_KIRK
130 139 KFVAN_KIRK
14  14  KFVAN_KIRK
140 149 KFVAN_KIRK
15  15  KFVAN_KIRK
150 159 KFVAN_KIRK
16  16  KFVAN_KIRK
160 169 KFVAN_KIRK
17  17  KFVAN_KIRK
170 179 KFVAN_KIRK
18  18  KFVAN_KIRK
180 189 KFVAN_KIRK
19  19  KFVAN_KIRK
190 199 KFVAN_KIRK
2   2   KFVAN_KIRK

2 2 MWLOVEJOY
20 20 KFVAN_KIRK
20 20 MWLOVEJOY
200 200 KFVAN_KIRK
201 209 MWLOVEJOY
21 21 MWLOVEJOY
21 30 KFVAN_KIRK
210 219 MWLOVEJOY
22 22 MWLOVEJOY
220 229 MWLOVEJOY
23 23 MWLOVEJOY
230 239 MWLOVEJOY
24 24 MWLOVEJOY
240 249 MWLOVEJOY
25 25 MWLOVEJOY
250 259 MWLOVEJOY
26 26 MWLOVEJOY
260 269 MWLOVEJOY
27 27 MWLOVEJOY
270 279 MWLOVEJOY
28 28 MWLOVEJOY
280 289 MWLOVEJOY
29 29 MWLOVEJOY
290 299 MWLOVEJOY
3 3 KFVAN_KIRK
3 3 MWLOVEJOY
30 39 KFVAN_KIRK
300 300 MWLOVEJOY
301 330 JWCASHEN

4   4   KFVAN_KIRK
40  49  KFVAN_KIRK
5   5   KFVAN_KIRK
50  59  KFVAN_KIRK
6   6   KFVAN_KIRK
60  69  KFVAN_KIRK
7   7   KFVAN_KIRK
70  79  KFVAN_KIRK
8   8   KFVAN_KIRK
80  89  KFVAN_KIRK
9   9   KFVAN_KIRK
90  99  KFVAN_KIRK

instead of

1       001     100     MWLOVEJOY
2       101     300     KFVAN_KIRK
3       301     330     JWCASHEN

So I just ignored the query and tried to build a new one to give the desired output.
But again, the output is different from specified. I thought you meant this:

SQL> select lpad(to_char(min(to_number(specimen_id))),3,'0') "MIN"

  2       , lpad(to_char(max(to_number(specimen_id))),3,'0') "MAX"
  3       , reader
  4    from ( select t.*
  5                , max(rn) over (partition by reader order by
to_number(specimen_id)) maxrn
  6             from ( select reading.*
  7                         , case lag(to_number(specimen_id)) over
(partition by reader order by to_number(specimen_id))
  8                           when to_number(specimen_id) - 1 then
null
  9                           else row_number() over (partition by
reader order by to_number(specimen_id))
 10                           end rn
 11                      from reading
 12                  ) t
 13         )
 14   group by reader
 15       , maxrn
 16   order by min
 17       , reader

18 /

MIN MAX READER
--- --- ----------
001 200 KFVAN_KIRK
001 100 MWLOVEJOY
101 190 JWCASHEN
201 300 MWLOVEJOY
301 330 JWCASHEN 5 rijen zijn geselecteerd.

Finally a tip: if a column is a number, it's best to give it a datatype number instead of varchar2. This way:
- you don't fool future maintainers of your code

less chance of invalid data entering your table
you possibly store less bytes
your queries will be easier to read
you don't fool the optimizer

Regards,
Rob. Received on Tue Jul 03 2007 - 03:21:53 CDT