1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Using loop in select statement

Discussion in 'SQL PL/SQL' started by waheeb, Aug 25, 2016.

  1. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen
    Hi every one,can i use select statement to return specific numbers?
    for example, user enter a number then the select statement returns all numbers starting from one to the entered number.like this:
    enter the number:6
    result:
    1
    2
    3
    4
    5
    6
    thanks
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    everything is very simple... read documentation
    Hierarchical Queries
    Code (SQL):
    SELECT level numb  FROM dual CONNECT BY level <= &your_number;
    recomendation:
    if you want to become the expert (developer), then try at first to try to solve the problem.
    if there are questions, then don't hesitate and ask.
     
    Last edited: Aug 25, 2016
  3. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen
    thanks .I'm in my first steps to becoming a developer and this this is my first time to know about 'connect by'
    or 'level' words.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    in the reference documentation given here it is told:

    1) CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy
    ... PRIOR expr = expr
    or
    ... expr = PRIOR expr

    PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.

    PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error.


    2) LEVEL Pseudocolumn
    For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A root row is the highest row within an inverted tree. A child row is any nonroot row. A parent row is any row that has children.