Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2012
    Posts
    13

    Unanswered: Using the LEVEL using WITH

    Hi,
    I would like to know, how LEVEL works?
    I have below query, which is not getting executed.

    /* Formatted on 2012/06/05 15:20 (Formatter Plus v4.8.7) */
    WITH rec AS
    (
    SELECT 'Vivek' first_name, 'Johari' last_name, 'Gudgaon' city, 110.15 sales
    FROM DUAL
    UNION ALL
    SELECT 'Sunil' first_name, 'Sharma' last_name, 'Gorakhpur' city, 125.09 sales
    FROM DUAL
    UNION ALL
    SELECT 'Chetan' first_name, 'Mehra' last_name, 'Indore' city, 124.89 sales
    FROM DUAL
    UNION ALL
    SELECT 'Ninad' first_name, 'Chauhan' last_name, '' city, 112.64 sales
    FROM DUAL
    UNION ALL
    SELECT 'Vinit' first_name, 'Gouda' last_name, 'Hubli' city, 110.9 sales
    FROM DUAL
    UNION ALL
    SELECT 'Rizvan' first_name, 'Shekh' last_name, '' city, 129.00 sales
    FROM DUAL
    UNION ALL
    SELECT 'Ram' first_name, 'Joshi' last_name, 'Pune' city, 105 sales
    FROM DUAL
    UNION ALL
    SELECT 'Alex' first_name, 'Parera' last_name, 'Pune' city, 110.70 sales
    FROM DUAL
    UNION ALL
    SELECT 'Rajiv' first_name, 'Naroola' last_name, 'Gudgaon' city, 131.63 sales
    FROM DUAL
    UNION ALL
    SELECT 'Ajay' first_name, 'Gandhi' last_name, 'Jaipur' city, 131.80 sales
    FROM DUAL
    UNION ALL
    SELECT 'Sandeep' first_name, 'Sharma' last_name, 'Jaipur' city, 121.80 sales
    FROM DUAL
    UNION ALL
    SELECT 'Vinod' first_name, 'Singh' last_name, '' city, 141.80 sales
    FROM DUAL)

    SELECT * --COUNT(*)
    FROM rec
    START WITH City = 'Pune'
    CONNECT BY PRIOR Sales > 110

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

  3. #3
    Join Date
    Jun 2012
    Posts
    13

    Using the LEVEL using WITH

    Hi,
    That is good explanation.
    Still I have query, why LEVEL not working using WITH

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    my car is not working
    tell me how to make my car go

    I don't know what you have.
    I don't know what you do.
    I don't know what you see.
    It is really, Really, REALLY difficult to fix a problem that can not be seen.
    use COPY & PASTE so we can see what you do & how Oracle responds.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Either I am missing something, or, you haven't tried to use LEVEL in your example you posted. Please post a SQL sample where you are trying to use LEVEL. Also include any error message that it gives when trying to execute the query.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Prasad09 View Post
    Still I have query, why LEVEL not working using WITH
    I'd say because you don't even mention it in your statement..
    But that's just a wild guess - assuming that something that isn't there, is likely to "not work"

  7. #7
    Join Date
    Jun 2012
    Posts
    13
    Hi,
    There was a confusion due to last row.
    Here is complete query.
    I just want to know if LEVEL works using WITH.

    /* Formatted on 2012/06/05 15:20 (Formatter Plus v4.8.7) */
    WITH rec AS
    (
    SELECT 'Vivek' first_name, 'Johari' last_name, 'Gudgaon' city, 110.15 sales
    FROM DUAL
    UNION ALL
    SELECT 'Sunil' first_name, 'Sharma' last_name, 'Gorakhpur' city, 125.09 sales
    FROM DUAL
    UNION ALL
    SELECT 'Chetan' first_name, 'Mehra' last_name, 'Indore' city, 124.89 sales
    FROM DUAL
    UNION ALL
    SELECT 'Ninad' first_name, 'Chauhan' last_name, '' city, 112.64 sales
    FROM DUAL
    UNION ALL
    SELECT 'Vinit' first_name, 'Gouda' last_name, 'Hubli' city, 110.9 sales
    FROM DUAL
    UNION ALL
    SELECT 'Rizvan' first_name, 'Shekh' last_name, '' city, 129.00 sales
    FROM DUAL
    UNION ALL
    SELECT 'Ram' first_name, 'Joshi' last_name, 'Pune' city, 105 sales
    FROM DUAL
    UNION ALL
    SELECT 'Alex' first_name, 'Parera' last_name, 'Pune' city, 110.70 sales
    FROM DUAL
    UNION ALL
    SELECT 'Rajiv' first_name, 'Naroola' last_name, 'Gudgaon' city, 131.63 sales
    FROM DUAL
    UNION ALL
    SELECT 'Ajay' first_name, 'Gandhi' last_name, 'Jaipur' city, 131.80 sales
    FROM DUAL
    UNION ALL
    SELECT 'Sandeep' first_name, 'Sharma' last_name, 'Jaipur' city, 121.80 sales
    FROM DUAL
    UNION ALL
    SELECT 'Vinod' first_name, 'Singh' last_name, '' city, 141.80 sales
    FROM DUAL)

    SELECT * --COUNT(*)
    FROM rec
    START WITH City = 'Pune'
    CONNECT BY PRIOR Sales > 110
    ORDER BY LEVEL;

    here is the error log.


    *** SCRIPT START : SessionTAGE@odsuq1(1) 6/8/2012 11:07:05 AM ***
    Processing ...
    WITH rec AS
    (
    SELECT 'Vivek' first_name, 'Johari' last_name, 'Gudgaon' city, 110.15 sales
    FROM DUAL
    UNION ALL
    SELECT 'Sunil' first_name, 'Sharma' last_name, 'Gorakhpur' city, 125.09 sales
    FROM DUAL
    UNION ALL
    SELECT 'Chetan' first_name, 'Mehra' last_name, 'Indore' city, 124.89 sales
    FROM DUAL
    UNION ALL
    SELECT 'Ninad' first_name, 'Chauhan' last_name, '' city, 112.64 sales
    FROM DUAL
    UNION ALL
    SELECT 'Vinit' first_name, 'Gouda' last_name, 'Hubli' city, 110.9 sales
    FROM DUAL
    UNION ALL
    SELECT 'Rizvan' first_name, 'Shekh' last_name, '' city, 129.00 sales
    FROM DUAL
    UNION ALL
    SELECT 'Ram' first_name, 'Joshi' last_name, 'Pune' city, 105 sales
    FROM DUAL
    UNION ALL
    SELECT 'Alex' first_name, 'Parera' last_name, 'Pune' city, 110.70 sales
    FROM DUAL
    UNION ALL
    SELECT 'Rajiv' first_name, 'Naroola' last_name, 'Gudgaon' city, 131.63 sales
    FROM DUAL
    UNION ALL
    SELECT 'Ajay' first_name, 'Gandhi' last_name, 'Jaipur' city, 131.80 sales
    FROM DUAL
    UNION ALL
    SELECT 'Sandeep' first_name, 'Sharma' last_name, 'Jaipur' city, 121.80 sales
    FROM DUAL
    UNION ALL
    SELECT 'Vinod' first_name, 'Singh' last_name, '' city, 141.80 sales
    FROM DUAL)

    SELECT * --COUNT(*)
    FROM rec
    START WITH City = 'Pune'
    CONNECT BY PRIOR Sales > 110
    ORDER BY LEVEL

    FROM DUAL
    *
    ORA-01436: CONNECT BY loop in user data

    *** Script stopped due to error ***
    *** SCRIPT END : SessionTAGE@odsuq1(1) 6/8/2012 11:07:07 AM ***

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Prasad09 View Post
    I just want to know if LEVEL works using WITH.
    Yes, it does.
    But apparently your data (or your connect by condition) creates an endless loop.

    See the manual for details http://docs.oracle.com/cd/E11882_01/...htm#SQLRF52335

    And please use [code] tags to format your SQL statements.
    For details see the forum help: http://www.dbforums.com/misc.php?do=bbcode#code
    Last edited by shammat; 06-08-12 at 03:57.

  9. #9
    Join Date
    Jun 2012
    Posts
    13

    Correct Query

    Hi Experts,
    I wanted to know how to use the LEVEL.
    But I had constructed incorrect structure to use LEVEL.
    Though query is not fetching all results.

    Please guide.

    WITH rec as
    (
    SELECT 0 employee_id, 1 manager_id, 'Rakesh' e_name FROM dual UNION ALL
    SELECT 1 employee_id, 2 manager_id, 'Renuka' e_name FROM dual UNION ALL
    SELECT 3 employee_id, 2 manager_id, 'Neeraj' e_name FROM dual UNION ALL
    SELECT 4 employee_id, 2 manager_id, 'Vikas' e_name FROM dual UNION ALL
    SELECT 5 employee_id, 4 manager_id, 'Bhumika' e_name FROM dual UNION ALL
    SELECT 6 employee_id, 4 manager_id, 'Dinkar' e_name FROM dual UNION ALL
    SELECT 7 employee_id, 6 manager_id, 'Rahul' e_name FROM dual UNION ALL
    SELECT 8 employee_id, 6 manager_id, 'Nita' e_name FROM dual UNION ALL
    SELECT 9 employee_id, 6 manager_id, 'Riya' e_name FROM dual UNION ALL
    SELECT 10 employee_id, 6 manager_id, 'Kamna' e_name FROM dual UNION ALL
    SELECT 11 employee_id, 6 manager_id, 'Vishal' e_name FROM dual UNION ALL
    SELECT 12 employee_id, 6 manager_id, 'Nisha' e_name FROM dual UNION ALL
    SELECT 13 employee_id, 6 manager_id, 'Gaurav' e_name FROM dual UNION ALL
    SELECT 14 employee_id, 8 manager_id, 'Ketan' e_name FROM dual UNION ALL
    SELECT 15 employee_id, 8 manager_id, 'Reena' e_name FROM dual
    )
    SELECT LEVEL, employee_id, manager_id, e_name
    FROM rec
    START WITH employee_id = 8
    CONNECT BY PRIOR employee_id = manager_id
    ORDER BY level

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by Prasad09
    Though query is not fetching all results
    It depends on what you call "all results". Query you wrote returned everything you told it to:
    Code:
         LEVEL EMPLOYEE_ID MANAGER_ID E_NAME
    ---------- ----------- ---------- -------
             1           8          6 Nita
             2          15          8 Reena
             2          14          8 Ketan
    
    SQL>
    As it starts with EMPLOYEE_ID = 8 (which is Nita), it also returns (because of the CONNECT BY clause) employees whose manager is Nita (i.e. records where MANAGER_ID = 8), and these are employees 14 & 15 (Ketan & Reena).

    Anyway: I think you don't really understand what LEVEL pseudocolumn is. What result do you expect? Could you describe it (both with words and manually written desired output)? Maybe it would help us help you.

  11. #11
    Join Date
    Jun 2012
    Posts
    13
    You are right. As I wanted to know how LEVEL works, I constructed this query.
    I want to display all the employees by their level.
    but what ?I am getting employee level for one level only.
    Can you help me with this?

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How will you, I or anyone recognize whether any posted response is correct or not?

    I don't know what problem needs to be solved or what a valid solution contains.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Prasad09 View Post
    You are right. As I wanted to know how LEVEL works, I constructed this query.
    I want to display all the employees by their level.
    but what ?I am getting employee level for one level only.
    Can you help me with this?
    Firstly you should realize, that LEVEL is not absolute measure - it is always related to the row level in the queried hierarchy. So, what about START WITH the employee managing more than two employees in total?

    Unfortunately, your sample data is incomplete, as rows with MANAGER_ID=2 do not reference any REC represented by EMPLOYEE_ID (they have manager which does not exist). Anyway, after fixing it adding e.g. this row
    Code:
    SELECT 2 employee_id, null manager_id, 'BIGBOSS' e_name FROM dual UNION ALL
    to your sample data, you may START WITH this new employee (or, better, start with rows with no manager, which is
    Code:
    START WITH manager_id is null
    ). Then you will get all employees with their level to all "bosses" (unless there are another employees with non-existent manager).

  14. #14
    Join Date
    Jun 2012
    Posts
    13

    Thumbs up

    Well, thanks for the reply.
    you are right, i missed to include employee with manager id as null (i.e. manager).
    I should have realized that.
    But thanks for your time and help.

Posting Permissions

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