Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Saturday, 2 April 2016

SQL Walkup Trees And Inline View

WALKUP TREES

Using hierarchical queries, you can retrieve data based on a natural hierarchical relationship between rows in a table. However, where a hierarchical relationship exists between the rows of a table, a process called tree walking enables the hierarchy to be constructed.

Ex:
    SQL> select ename || '==>' ||  prior ename, level from emp start with ename = 'KING' connect by prior empno=mgr;

ENAME||'==>'||PRIORENAM      LEVEL
------------------------------------   --------
KING==>                                1
JONES==>KING                     2
SCOTT==>JONES                   3
ADAMS==>SCOTT                  4
FORD==>JONES                     3
SMITH==>FORD                     4
BLAKE==>KING                      2
ALLEN==>BLAKE                    3
WARD==>BLAKE                    3
MARTIN==>BLAKE                 3
TURNER==>BLAKE                 3
JAMES==>BLAKE                    3
CLARK==>KING                      2
MILLER==>CLARK                  3

In the above
Start with clause specifies the root row of the table.
Level pseudo column gives the 1 for root , 2 for child and so on.
Connect by prior clause specifies the columns which has parent-child relationship.


INLINE VIEW OR TOP-N ANALYSIS

In the select statement instead of table name, replacing the select statement is known as inline view.

Ex:
     SQL> Select ename, sal, rownum rank from (select *from emp order by sal);

ENAME             SAL       RANK
---------- ---------- ----------
SMITH             800          1
JAMES             950          2
ADAMS            1100        3
WARD             1250         4
MARTIN           1250        5
MILLER           1300         6
TURNER           1500        7
ALLEN            1600          8
CLARK            2450          9
BLAKE            2850         10
JONES            2975         11
SCOTT            3000         12
FORD             3000          13

KING             5000          14
Share this article :

4 comments:

  1. Hello Dayakar,

    Smokin hot stuff! You’ve trimmed my dim. I feel as bright and fresh as your prolific website and blogs!

    I have a source server (Lets say ServerA) from where I'm pulling data from. It has less memory. But my ssis packages are deployed in SSIS server (ServerB) which has lot of memory.
    The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.
    In script component, I'm pulling the data into data table and querying it in the transformation (Range lookup). In this scenario, when the data table is built in Script component, where is it stored? Is it stored in memory of ServerA or ServerB?

    I look forward to see your next updates.

    Many Thanks,
    John

    ReplyDelete
  2. Halo,


    Fully agree on Halo,


    Fully agree on SQL Walkup Trees And Inline View . We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.

    My table column type is clob. Field value contain double quotes

    id,text_clob,date

    1,"\"data1\",0,1,1,1,\"data2\"","2018-03-03 09:08:45"

    can you kindly help loader query. i cant use replace in clob field.

    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!



    Thank you,

    . We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.

    My table column type is clob. Field value contain double quotes

    id,text_clob,date

    1,"\"data1\",0,1,1,1,\"data2\"","2018-03-03 09:08:45"

    can you kindly help loader query. i cant use replace in clob field.

    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!



    Thank you,

    ReplyDelete
  3. Sain Bainuu,


    In total awe…. So much respect and gratitude to you folks for pulling off such amazing blogs without missing any points on the #topic. Kudos!


    I am facing an issue with DBMS_CHAIN execution.

    Issue :Even one of the step got Failed, remaining steps were processed with out waiting for depended step SUCCESS.

    Just FYI: I created 2 chains i.e. Chain1 and Chain2 . Calling Chain2 as subchain in Chain1.

    i attached the total script for your reference and below the run details.

    In the below run_details , you can see STEP2 is in failed state but further steps(i.e. STEP3 of CHAIN1 and STEP1 , STEP2 of CHAIN2 got processed)

    Please suggest me on this(what i need to do to hold at FAILED step and how can i RERUN that FAILED step only (or) from Failed Steps).
    I read multiple articles and watched many videos about how to use this tool - and was still confused! Your instructions were easy to understand and made the process simple.


    Many Thanks,

    ReplyDelete
  4. Hello Dayakar,

    SQL Walkup Trees And Inline View being contrived to exist for many projects simply so it can be run will be the first to hit the wall, but those projects where the functions to make existing transactions cheaper in real world applications will find the elusive real world demand.

    I am trying to use this in a materialized view and got below error:

    SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query

    12015. 00000 - "cannot create a fast refresh materialized view from a complex query"

    *Cause: Neither ROWIDs and nor primary key constraints are supported for

    complex queries.

    *Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE

    option or create a simple materialized view.

    select IR.rowid MV_INST_LOBR_ROWID, J.rowid Job_ROWID, J.* FROM JOB J

    LEFT JOIN MV_INST_LOB_R IR ON

    (IR.I1503_CUST_AC_NO_PT1 = J.I3200_CUST_AC_NO_PT1 AND IR.I1503_CUST_AC_NO_PT2 = J.I3200_CUST_AC_NO_PT2 AND IR.I1503_INST_SEQ_NO = J.I3200_INST_SEQ_NO)

    WHERE IR.I1503_CUST_AC_NO_PT1 IS NOT NULL AND IR.I1503_CUST_AC_NO_PT2 IS NOT NULL

    UNION SELECT null, J.rowid Job_ROWID, J.* FROM JOB J WHERE J.I3200_CUST_AC_NO_PT1 IS NULL AND J.I3200_CUST_AC_NO_PT1 IS NULL;

    Basically, the intention is to get all the records joining MV_INST_LOB_R and JOB table
    where MV_INST_LOB_R do not have record for the joining condition J.I3200_CUST_AC_NO_PT1 / J.I3200_CUST_AC_NO_PT2

    Can you help me in converting to simple query, so that I can use simple materialized view.

    Follow my new blog if you interested in just tag along me in any social media platforms!

    Merci,
    Preethi.

    ReplyDelete