Home » SQL & PL/SQL » SQL & PL/SQL » select cntr from dual (?) where cntr between "20" and "25"  () 1 Vote
select cntr from dual (?) where cntr between "20" and "25" [message #210368] Wed, 20 December 2006 08:53 Go to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
is it possible to have a dummy-query that produces results between a specific interval (given in the where clause)

for example:
select cntr from dual (?) where cntr between "20" and "25"

cntr
----
20
21
22
23
24
25

[Updated on: Wed, 20 December 2006 08:53]

Report message to a moderator

Re: select cntr from dual (?) where cntr between "20" and "25" [message #210370 is a reply to message #210368] Wed, 20 December 2006 08:55 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Quick search would reveal Oracle Row Generator Techniques.
Re: select cntr from dual (?) where cntr between "20" and "25" [message #210375 is a reply to message #210368] Wed, 20 December 2006 09:17 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
yeah,

i did
select n from(
SELECT ROWNUM n
FROM ALL_OBJECTS)
WHERE n > 20 and n < 30

this works, but only for numbers until 21448.
i have to work with phone numbers, which consist of 15 digits.

I have to list all numbers between for example
00331234567880 and 00331234567890
Re: select cntr from dual (?) where cntr between "20" and "25" [message #210377 is a reply to message #210375] Wed, 20 December 2006 09:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Then read on. There are more examples in the Wiki then just the one using all_objects.

and remember you only need N2-N1 rows. Not 10E15.

[Updated on: Wed, 20 December 2006 09:19]

Report message to a moderator

Re: select cntr from dual (?) where cntr between "20" and "25" [message #210544 is a reply to message #210368] Thu, 21 December 2006 04:51 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
quite difficult for a newbee Embarassed
unfortunately it doesn't help.
Re: select cntr from dual (?) where cntr between "20" and "25" [message #210551 is a reply to message #210544] Thu, 21 December 2006 05:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
OK, so suppose you need all numbers between 123456789012 and 123456789121.
You then need a row generator that generates 123456789121 minus 123456789012 rows.

Try that and show us your results.
Re: select cntr from dual (?) where cntr between "20" and "25" [message #210555 is a reply to message #210551] Thu, 21 December 2006 05:21 Go to previous messageGo to next message
Martin Eysackers
Messages: 80
Registered: October 2005
Location: Belgium
Member
the easiest way to generate rows is to use connect by level <= number of rows wanted
on dual

this would do the trick :

select level, level + (123456789012 - 1)
from dual
connect by level <= (123456789121 - 123456789012)

I used that trick to generate something like
00:00
00:05
00:10
00:15
00:20
.
.
23:50
23:55
00:00
Re: select cntr from dual (?) where cntr between "20" and "25" [message #210740 is a reply to message #210368] Fri, 22 December 2006 02:38 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
ok thanks to all.... that works now.... but...

my goal to have this querry is the following, but i don't manage to write the final querry.

i have a table:

id, range_min, range_max
1,10,20
2,21,23
3,101,105

now i'm looking for (exactly ONE querry) that produces the following, out of the table above:
tmp_pk,old_id,range_min,range_max
1,1,10,10
2,1,11,11
3,1,12,12
4,1,13,13
5,1,14,14
6,1,15,15
7,1,16,16
8,1,17,17
9,1,18,18
10,1,19,19
11,1,20,20
12,2,21,21
13,2,22,22
14,2,23,23
15,3,101,101
16,3,102,102
17,3,103,103
18,3,104,104
19,3,105,105

i thought i would manage to reach my goad easily upon the querry above (that increments between ranges),
but appearently it's much more complicated then i could ever think.
Re: select cntr from dual (?) where cntr between "20" and "25" [message #210748 is a reply to message #210740] Fri, 22 December 2006 03:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The first column can be generated using rownum
The rest can be generated using a union all. Use that as inline view.
Re: select cntr from dual (?) where cntr between "20" and "25" [message #210783 is a reply to message #210748] Fri, 22 December 2006 05:58 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This could, perhaps, be one way to get what you need:
SQL> SELECT * FROM test;

        ID  RANGE_MIN  RANGE_MAX
---------- ---------- ----------
         1         10         20
         2         21         23
         3        101        105

SQL> SELECT ROWNUM, a.id, a.range_min - 1 + b.lvl res
  2  FROM (SELECT id, range_min FROM TEST) a,
  3      (SELECT DISTINCT id, LEVEL lvl FROM TEST
  4       CONNECT BY LEVEL < range_max - range_min + 2
  5       ORDER BY 1, 2
  6      ) b
  7  WHERE a.id = b.id;

    ROWNUM         ID        RES
---------- ---------- ----------
         1          1         10
         2          1         11
         3          1         12
         4          1         13
         5          1         14
         6          1         15
         7          1         16
         8          1         17
         9          1         18
        10          1         19
        11          1         20
        12          2         21
        13          2         22
        14          2         23
        15          3        101
        16          3        102
        17          3        103
        18          3        104
        19          3        105

19 rows selected.

SQL>
Re: select cntr from dual (?) where cntr between "20" and "25" [message #210800 is a reply to message #210368] Fri, 22 December 2006 07:49 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
you are terribly good !!!!!
i finally have the querry i need.
(the "connecty by level"-thing is totally new and "black box" for me)

[Updated on: Fri, 22 December 2006 07:50]

Report message to a moderator

Re: select cntr from dual (?) where cntr between "20" and "25" [message #210842 is a reply to message #210368] Fri, 22 December 2006 11:18 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
there is still a problem.
it's a performance killer on a table where 60.000 ranges have to be "extracted".
the query remains unfinished, even after 30 minutes ! Sad

[Updated on: Fri, 22 December 2006 11:19]

Report message to a moderator

Re: select cntr from dual (?) where cntr between "20" and "25" [message #210847 is a reply to message #210842] Fri, 22 December 2006 11:29 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is there an index on 'id' column? If not, could you create one using

CREATE INDEX i1_test_id ON test (id);

and try again?
Re: select cntr from dual (?) where cntr between "20" and "25" [message #210867 is a reply to message #210368] Fri, 22 December 2006 14:40 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

With MODEL we can avoid the joins , distinct sorts and cross products.


curamgph>desc foo;
Name Null? Type
----------------------------------------- -------- ------------
ID NUMBER
RMN NUMBER
RMX NUMBER



curamgph>select * from foo;

ID RMN RMX
---------- ---------- ----------
1 10 20
2 21 23
3 102 105





select rownum rn,id,res from
(
select id,rmn res from foo
model return all rows
main grr
partition by (id)
dimension by (0 as dmn )
measures (rmn,rmx)
rules iterate(9000000) until
rmn[iteration_number]=rmx[iteration_number]
(
rmn[iteration_number]=rmn[0]+iteration_number,
rmx[iteration_number]=rmx[0]
)
)
mgph>
mgph>/

RN ID RES
------ ---------- ----------
1 1 10
2 1 11
3 1 12
4 1 13
5 1 14
6 1 15
7 1 16
8 1 17
9 1 18
10 1 19
11 1 20
12 2 21
13 2 22
14 2 23
15 3 102
16 3 103
17 3 104
18 3 105
=============

1) If the FOO table is "BIG" and you are picking a few ranges out of it , then create an index on the id column.
Else FTS is better.
2) You can parallize the query if you have multiple cpus and PQ is enabled.







Srini

Re: select cntr from dual (?) where cntr between "20" and "25" [message #210870 is a reply to message #210867] Fri, 22 December 2006 16:50 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I admit: viewing your query, I felt like an Earthman who first saw an Alien ./fa/450/0/ The only known words were SELECT and FROM. OMG, I'll spend next year to figure out what (actually, how) it works ./fa/1637/0/
Re: select cntr from dual (?) where cntr between "20" and "25" [message #210936 is a reply to message #210870] Sat, 23 December 2006 19:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Don't worry Littlefoot - there's at least one more Earthman down here looking up.

@srinivnp - Can We prevail upon you to give us an explanation of how your Model clause works?
Re: select cntr from dual (?) where cntr between "20" and "25" [message #210954 is a reply to message #210936] Sun, 24 December 2006 02:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
tsk gentleman.
There is this nice little site you can search for these kind of things!
./fa/1681/0/

But this is the first time I see the model clause being used, I must admit..
Re: select cntr from dual (?) where cntr between "20" and "25" [message #210960 is a reply to message #210954] Sun, 24 December 2006 05:06 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you, both of you. But, do I feel any better about the issue? Nope. Razz
Re: select cntr from dual (?) where cntr between "20" and "25" [message #210996 is a reply to message #210960] Sun, 24 December 2006 20:42 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

RMN and RMX are lower and upper bounds within which we have to generate a sequence of values , uniformly increasing by 1.

Since the range between RMN and RMX can vary , iterative model is the best choice.

A uniformly increasing value is given by the Oracle variable iteration_number.

9000000 is an arbitrary high value for the number of iterations. Only requirement here is that it should be equal to the expected maximum of (RMX-RMN). I not sure just use the highest number that Oracle can support.

However we get out of the iterative loop when the newly assigned values of RMX and RMN are equal , through the condition "until rmn[iteration_number]=rmx[iteration_number]".
That makes the SQL efficient by avoiding unnecessary calculations.

The next point is to use the iteration_number itself as the dimension value .

That brings as to the dimesion of the MODEL.
There are no logically good fields that can be used as dimension in the given data. So an arbitrary dimension is introduced by the clause "dimension by (0 as dmn )". Also starting the dimension value at 0 is also required .


Oracle increments iteration_number by 1 with each iteration and by using iteration_number itself as the dimension value , we refer to the current measure values.

Next trick is to increment RMIN by 1 for each iteration , which is easy by using the iteration_number itself as the value to be added to the base value ( first value ) of RMN.That is what
"rmn[iteration_number]=rmn[0]+iteration_number " does.

However we keep the value of RMX the same as the base value , in all the iterations as it is clear from the clause
"rmx[iteration_number]=rmx[0]"

The "partition by id" clause ensures that these calculations start afresh for each of the values of id.

I always name my MODELS. grr is just such a name for the main model.
=====

And the outer query is straightforward.


Srini















Re: select cntr from dual (?) where cntr between "20" and "25" [message #211014 is a reply to message #210996] Mon, 25 December 2006 02:49 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you for explaining the whole thing! I still feel dizzy about it ...
Re: select cntr from dual (?) where cntr between "20" and "25" [message #211078 is a reply to message #210368] Tue, 26 December 2006 02:45 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
@ srinivnp : thank you very much. i think these "model"-statements can be very useful in future, but unfortunately we're on oracle9i and thus can not yet use it.
@ littlefoot: there was already an index on the "id"-colum, so on this we can not improve

any other ideas to improve it on an oracle9i ?

[Updated on: Tue, 26 December 2006 02:46]

Report message to a moderator

Re: select cntr from dual (?) where cntr between "20" and "25" [message #211125 is a reply to message #210368] Tue, 26 December 2006 09:39 Go to previous message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

If you are on 9i, and the involved ranges , that is (RMX-RMN) is "LARGE" and/or the number of distinct id involved are "LARGE" , then a pipelined table function will be the best case. For this condition PLSQL will outperform SQL for 9i.

Let the table function accept a cursor variable ( select id, rmin , rmx from foo ) as input.
Loop through it , and pipe out the values.

In your client program do select column_value from table(your_function ) ; And then do whaterver else you have to do.

Srini



Previous Topic: delete case sensetive
Next Topic: Sending Email From Oracle
Goto Forum:
  


Current Time: Sun Dec 04 10:49:46 CST 2016

Total time taken to generate the page: 0.20392 seconds