Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: connect by level

    Why does this bring back 1 row:

    Code:
     select Level 
     from dual 
     connect By Level <= 0
    I was hoping that this would bring back 0-> many records depending upon whether THE_VAL was = to 0->many

    Code:
     select Level 
     from dual 
     connect By Level <= THE_VAL
    --=cf

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The LEVEL pseudocolumn will return 1 for a root row returned by a query; your example, as well as negative values, return only one row so query also returns 1.

    If you want to return 0 as a result, subtract 1 from LEVEL:
    Code:
    SELECT LEVEL  - 1
    FROM dual
    CONNECT BY LEVEL <= &the_val

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    But that gives back 1 row with the values of 0 for both

    Code:
    SELECT LEVEL  - 1
    FROM dual
    CONNECT BY LEVEL <= 0
    and
    Code:
    SELECT LEVEL  - 1
    FROM dual
    CONNECT BY LEVEL <= 1
    I went ahead and put this query into an inline view, and set a WHERE clause outside of that which returned no rows in the case that &the_val was 0.

    --=cf

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm afraid I don't understand what you are trying to explain ...

    First you did this:
    Code:
    SQL> select level lvl
      2  from dual
      3  connect by level <= 0;
    
           LVL
    ----------
             1
    Then you put it into an inline view and added a WHERE clause. Something like this?
    Code:
    SQL> select lvl
      2  from (select level lvl
      3        from dual
      4        connect by level <= 0
      5       )
      6  where lvl = 1;
    
           LVL
    ----------
             1
    
    SQL>
    I don't think so, as I didn't get 'no rows selected' ... Could you post your example, please?

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    The SQL I have in my PACKAGE looks like the following (although this code is actually a subselect of a larger SQL as well)

    Code:
    select Rnum
    from (
          select Level Rnum 
          from dual 
          connect By Level <= trunc(WK_ADJ_END_DATE) - trunc(WK_ADJ_BEG_DATE) 
         )
    where trunc(WK_ADJ_END_DATE) <> trunc(WK_ADJ_BEG_DATE)

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see; something like this?
    Code:
    SQL> select rnum
      2  from (select level rnum
      3        from dual
      4        connect by level <= 1 - 1
      5       )
      6  where 1 <> 1;
    
    no rows selected
    
    SQL>
    As you've said, "THE_VAL = 0" only if
    Code:
    trunc(WK_ADJ_END_DATE) = trunc(WK_ADJ_BEG_DATE)
    At the same time, WHERE condition wants
    Code:
    trunc(WK_ADJ_END_DATE) <> trunc(WK_ADJ_BEG_DATE)
    As far as I understand it, it is NOT "connect by level" causing the 'no data found' but the WHERE clause you wrote.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    I'm where I want to be now. In the beginning, I expected the following queries to return different results. Namely, I thought the first would return no rows

    Code:
    SQL> select level rnum
      2  from dual
      3  connect by level <= 1 - 1;
    
          RNUM
    ----------
             1
    
    SQL> select level rnum
      2  from dual
      3  connect by level <= 2 - 1;
    
          RNUM
    ----------
             1
    So, to fix that, I added the WHERE clause to perform that extra step

    Code:
    SQL> select rnum
      2  from (select level rnum
      3        from dual
      4        connect by level <= 1 - 1
      5       )
      6  where 1 <> 1;
    
    no rows selected
    
    SQL> select rnum
      2  from (select level rnum
      3        from dual
      4        connect by level <= 2 - 1
      5       )
      6  where 2 <> 1;
    
          RNUM
    ----------
             1
    --=cf

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Excuse me, but - where's the point in doing all of this? I don't see it, I must admit.

    You've already had that result, without an inline view and additional WHERE clause, didn't you? Those two queries return the same result:
    Code:
                                           SQL> select rnum from
    SQL> select level rnum            <=>    2      (select level rnum
      2  from dual                    <=>    3        from dual
      3  connect by level <= 2 - 1;   <=>    4        connect by level <= 2 - 1
                                             5       )
          RNUM                               6  where 2 <> 1;
    ----------
             1                               RNUM
                                             ----------
                                                      1
    After all, having it said that "2 <> 1", we'd get the same result saying that "1 = 1":
    Code:
    SQL> l
      1  select rnum
      2  from (select level rnum
      3        from dual
      4        connect by level <= 2 - 1
      5       )
      6* where 1 = 1
    SQL> /
    
          RNUM
    ----------
             1
    which is, furthermore, the same as if there was no WHERE clause at all:
    Code:
    SQL> del
    SQL> l
      1  select rnum
      2  from (select level rnum
      3        from dual
      4        connect by level <= 2 - 1
      5*      )
    SQL> /
    
          RNUM
    ----------
             1
    So, excuse me once again, but - which witches are we chasing here, exactly?

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    Trying to tell the difference between the following is the problem, when is the result of the comparison "1" and when is it "0"

    Code:
    SQL> select level rnum
      2  from dual
      3  connect by level <= 1 - 1;
    
          RNUM
    ----------
             1
    and
    Code:
    SQL> select level rnum
      2  from dual
      3  connect by level <= 2 - 1;
    
          RNUM
    ----------
             1
    They both return the same result, which breaks my code. I want 0 records returned from the first query, and one record (with the value of "1" for RNUM) in the second.

    Before. we queried the ALL_OBJECTS table, instead of DUAL with the CONNECT BY, and it didn't scale. So, I am transitioning the code over to the DUAL approach, without having to rewrite too much code. So, using the older code

    Code:
    select rownum rnum
    from all_objects
    where rownum <= 1-1
    that brings back 0 records.

    ---=Chuck

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oh, thank you! I *think* I understand now.

    So, post #7 says that you're satisfied with the code, right? I don't have to dream about it any more, right?

    Please, say: "Right!"

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074

    Smile

    Chuck happy ... yes.

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    LOL!!! hahahahhah!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •