Forum Stats

  • 3,838,561 Users
  • 2,262,383 Discussions
  • 7,900,687 Comments

Discussions

SQL query to get number from 0 to 99

Yash Parashar
Yash Parashar Member Posts: 6 Green Ribbon
edited Jun 15, 2022 8:07AM in SQL & PL/SQL

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

  • cormaco
    cormaco Member Posts: 1,952 Silver Crown
    Answer ✓

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


  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,856 Silver Crown
    edited Jun 14, 2022 6:01PM Answer ✓

    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
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,856 Silver Crown

    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
    Solomon Yakobson Member Posts: 19,594 Red Diamond

    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

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond

    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.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,132 Red Diamond


    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
    cormaco Member Posts: 1,952 Silver Crown
    Answer ✓

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


  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,856 Silver Crown
    edited Jun 14, 2022 6:01PM Answer ✓

    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
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond

    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
    Yash Parashar Member Posts: 6 Green Ribbon
    edited Jun 15, 2022 5:01AM

    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.

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,856 Silver Crown

    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
    Yash Parashar Member Posts: 6 Green Ribbon

    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
    Solomon Yakobson Member Posts: 19,594 Red Diamond

    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.