Oracle Pseudocoloumns : LEVEL | Oracle PL/SQL Interview Questions

LEVEL
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. A leaf row is any row without children. Figure 2-1 shows the nodes of an inverted tree with their LEVEL values.
left

 
To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses.

3 comments:

  1. .Hi Bro,


    Nice to be visiting your blog again, it has been months for me. Well this article that i’ve been waited for so long.
    CentOS 7.4 with PHP 7.0.10 running from the command line
    SQLSRV driver version is 4.3.0 (according to 'php --ri sqlsrv')
    Microsoft SQ0L Server version 2014
    I am developing a PHP solution to query SQL server database that will return millions of records so I can't use my standard approach of fetching the entire result set into an array. In addition to this, I need to do some additional calculations and subtotaling of the result data. I am thinking the proper approach is to use cursors. So, per the documentation, I added
    PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL and PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_STATIC to my prepare statement but I get an error when I try to use fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, -1) or fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_PRIOR)
    PHP Fatal error: Uncaught PDOException: SQLSTATE[HY106]: [Microsoft][ODBC Driver 13 for SQL Server]Fetch type out of range in script.php
    I don't have problem when I use fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT) or fetch() without arguments. I haven't modified the query at all to use cursors and maybe that's my problem. The query doesn't use stored procedures. It's is basically a complex select query
    Any ideas on what I could be doing wrong?
    Thank you very much and will look for more postings from you.


    Thanks,
    Irene Hynes

    ReplyDelete
  2. Hey,


    I learnt so much in such little time about Oracle Pseudocoloumns : LEVEL | Oracle PL/SQL Interview Questions . Even a toddler could become smart reading of your amazing articles.
    I have requirement like

    select COL1,Col2 from A
    minus
    Select COL1,COL2 from B;

    both tables have 50+ millions record so I want to automate a job which will execute this query for 1millions records for one time and then again 1 millions for second time...like loop and store output in record type.But nice Article Mate! Great Information! Keep up the good work!


    Kind Regards,

    ReplyDelete
  3. OlĂ ,


    Grazie! Grazie! Grazie! Your blog is indeed quite interesting around #topic! I agree with you on lot of points!


    how to get the first non space character from a string in Oracle. Could you help me on this?





    Anyways great write up, your efforts are much appreciated.


    Many Thanks,

    ReplyDelete

Please Provide your feedback here