Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
SQL query to get number from 0 to 99

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.
Best Answers
-
Chose a different Maximum Rows Preference from the Actions menu, the default is 50, max is 5000
-
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:
... ...
-
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.
-
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.
Answers
-
Hi, @User_025ZP
What is the exact output you want? In the query you posted, the sub-query produces this result set:
0 1 2 ... 9999998 9999999
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.
-
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).
-
Chose a different Maximum Rows Preference from the Actions menu, the default is 50, max is 5000
-
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:
... ...
-
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 ;
-
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.
-
thanks for the reference my friend
-
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.
-
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.
-
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.