SQL query to get number from 0 to 99

Yash Parashar · Jun 14, 2022 11:58AM

I already tried this program

SELECT LEVEL - 1
FROM DUAL
CONNECT BY LEVEL <= 10000000

on "https://livesql.oracle.com/apex/f?p=590:1:15240902803423::LEVEL1:RP::#" but it prints only 0 to 49. screen shot attached.

Note : I also download that below mentioned csv file, but it has the same data as shown on website.

Also I need a little help to print table of 2 in "2 x 1 = "' format. with using LOOP/IF/CASE or any of these.

cormaco · Jun 14, 2022 4:52PM

Chose a different Maximum Rows Preference from the Actions menu, the default is 50, max is 5000

L. Fernigrini · Jun 14, 2022 5:59PM

As others mentioned, it is a problem of the UI that you can fix by changing the number of result shown.

Regarding your second question, you can generate those results like this:

SELECT '2 * ' || TO_CHAR(LEVEL) || ' = ' || TO_CHAR(2 * LEVEL)
FROM DUAL
CONNECT BY LEVEL <= 100;

Results:

...
...

L. Fernigrini · Jun 15, 2022 11:09AM

https://community.oracle.com/tech/developers/discussion/comment/16838674#Comment_16838674

As you can see for the first answers, CONNECT BY LEVEL <= X allows you to "create" X rows starting with a single one (DUAL is a table in Oracle that has only 1 row). This is one of the most common and simple ways to "generate" rows. CONNECT BY is an operator designed to work with hierarchy queries, so LEVEL returns the level in the hierarchy. When used to "generate" rows, knowing that LEVEL can be used as a "row number".

Regarding my answer, what I am doing is to contatenate (using the || contatenation operator) a fixed string ("2 * ") with the row number (LEVEL) converted to string (using (TO_CHAR) with another fixed string (" = ") with the resulf of 2 * LEVEL converted again to string.

Solomon Yakobson · Jun 15, 2022 12:48PM

Keep in mind, hierarchical query has limitations:

SQL> select count(*) from dual connect by level <= 3000000;

  COUNT(*)
----------
   3000000

SQL> select count(*) from dual connect by level <= 4000000;
select count(*) from dual connect by level <= 4000000
                     *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation

SQL>

So if you need to generate large sequence you need some other method, for example xmltable:

SQL> select count(*) from xmltable('1 to 4000000');

  COUNT(*)
----------
   4000000

SQL> select count(*) from xmltable('1 to 10000000');

  COUNT(*)
----------
  10000000

SQL> select count(*) from xmltable('1 to 100000000');

  COUNT(*)
----------
 100000000

SQL>

SY.

Frank Kulash · Jun 14, 2022 12:20PM

Hi, @User_025ZP

What is the exact output you want? In the query you posted, the sub-query produces this result set:

a total of 10000000 rows. If you want only 0 to 99 (100 rows), then use

LEVEL <= 100

in the CONNECT BY clause.

Also I need a little help to print table of 2 in "2 x 1 = "' format.

Again, post the exact output you want.

BluShadow · Jun 14, 2022 12:37PM

https://community.oracle.com/tech/developers/discussion/4499709/sql-query-to-get-number-from-0-to-99

Look at your screenshot... at the bottom...

It clearly says that more rows of data exist. It just hasn't retrieved more than the first 50 rows.

That's up to the interface that is displaying the results, not the query. Your query will clearly generate 10000000 rows when used in PL/SQL code or an interface that queries all the rows back.

If I run the same query in TOAD, I get..

... the first 500 rows, and it will query more if I keep scrolling down.

If I ran it in SQL*Plus I would get all the rows (eventually) returned because it has no mechanism to 'paginate' the data. (I'm not going to do that because querying that many rows would be silly)

So, your live SQL through Apex is the thing limiting the output to 50 rows (initially).

cormaco · Jun 14, 2022 4:52PM

Chose a different Maximum Rows Preference from the Actions menu, the default is 50, max is 5000

L. Fernigrini · Jun 14, 2022 5:59PM

As others mentioned, it is a problem of the UI that you can fix by changing the number of result shown.

Regarding your second question, you can generate those results like this:

SELECT '2 * ' || TO_CHAR(LEVEL) || ' = ' || TO_CHAR(2 * LEVEL)
FROM DUAL
CONNECT BY LEVEL <= 100;

Results:

...
...

Frank Kulash · Jun 14, 2022 10:57PM

Also I need a little help to print table of 2 in "2 x 1 = "' format.

Are you looking for something like this

  N TIMES_2 TIMES_3 TIMES_4
--- ------- ------- -------
  0       0       0       0
  1       2       3       4
  2       4       6       8
  3       6       9      12
  4       8      12      16

? If so, do something like this:

WITH  all_n  AS
(
  SELECT LEVEL - 1 AS n
  FROM   dual
  CONNECT BY LEVEL <= 5
)
,  products  AS
(
  SELECT  a.n
  ,	  b.n	    AS multiplier
  ,	  a.n * b.n AS product
  FROM    all_n a
  CROSS JOIN all_n b
)
SELECT   *
FROM	 products
PIVOT	 (  MIN (product)
	 FOR multiplier IN ( 2 AS times_2
	    		   , 3 AS times_3
			   ,	 4 AS times_4
			   )
	 )
ORDER BY n
;

Yash Parashar · Jun 15, 2022 4:23AM

https://community.oracle.com/tech/developers/discussion/comment/16838638#Comment_16838638

Thanks friend it works fine, but can you please help to understand the working of this code if you ok with it. that will be very helpful for me.

Yash Parashar · Jun 15, 2022 5:02AM

https://community.oracle.com/tech/developers/discussion/comment/16838631#Comment_16838631

thanks for the reference my friend

L. Fernigrini · Jun 15, 2022 11:09AM

https://community.oracle.com/tech/developers/discussion/comment/16838674#Comment_16838674

As you can see for the first answers, CONNECT BY LEVEL <= X allows you to "create" X rows starting with a single one (DUAL is a table in Oracle that has only 1 row). This is one of the most common and simple ways to "generate" rows. CONNECT BY is an operator designed to work with hierarchy queries, so LEVEL returns the level in the hierarchy. When used to "generate" rows, knowing that LEVEL can be used as a "row number".

Regarding my answer, what I am doing is to contatenate (using the || contatenation operator) a fixed string ("2 * ") with the row number (LEVEL) converted to string (using (TO_CHAR) with another fixed string (" = ") with the resulf of 2 * LEVEL converted again to string.

Yash Parashar · Jun 15, 2022 11:13AM

just to make the it simple and easy to understand for Newcomer just like me.

printing counting create a table with at least one column with any type of data. then use this code

select rownum from table_name connect by rownum <=100 you can change this value as per your need. Here assume 'connect by' is assigning a these values.

Now to print a table use select 2*rownum(i) from table_name connect by rownum<=10(ii) you can multiply (i) with any number you want to print table of and can assign the value to (ii) as per your need if you want to print more of it.

ALL THANKS TO THE SENIOR DEVELOPER HELP ME TO UNDERSTAND

This discussion ends here.

Solomon Yakobson · Jun 15, 2022 12:48PM

Keep in mind, hierarchical query has limitations:

SQL> select count(*) from dual connect by level <= 3000000;

  COUNT(*)
----------
   3000000

SQL> select count(*) from dual connect by level <= 4000000;
select count(*) from dual connect by level <= 4000000
                     *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation

SQL>

So if you need to generate large sequence you need some other method, for example xmltable:

SQL> select count(*) from xmltable('1 to 4000000');

  COUNT(*)
----------
   4000000

SQL> select count(*) from xmltable('1 to 10000000');

  COUNT(*)
----------
  10000000

SQL> select count(*) from xmltable('1 to 100000000');

  COUNT(*)
----------
 100000000

SQL>

SY.

Forum Stats

Quick Links

Categories

SQL query to get number from 0 to 99

Best Answers

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

SQL query to get number from 0 to 99

Best Answers

Answers