Hierarchical data by Oracle

Applications Grid / Reports
#1

Hi,

Anyone ever made some grid with hierarchical query in Oracle?
I have a query, that run well in my sql editor, and I also tested it in NetMake SQL Builder. Works fine.
It goes like this


SELECT
    b.ittrobjscode, b.itmsobjsid, b.ittrobjpscode, b.itmsobjparentsid,
    LEVEL as LEVELX,
    SYS_CONNECT_BY_PATH(B.itmsobjsid, '->') as ROOT,
    RPAD(' ', LEVEL, '>') || b.itmsobjsid AS OBJECT_CHILD
    FROM IT_TRN_OBJECTS B
    START WITH ittrobjscode = '001'
    CONNECT BY PRIOR B.ittrobjscode=B.ittrobjpscode

But no grid is generated by this script. It always returns error when I tried to make new grid application. Can anyone tell me what is wrong?

#2

I assume your sql is correct. Can you make a view from this statement and then use the view?
I am assuming that the sql parser from scriptcase doesnt handle START WITH and CONNECT BY. You can verify this by removing those two lines… I am not sure tho.

#3

I tried to make a view as rr suggested. And it turns out it worked. :smiley:
I just have to add another START WITH clause onScriptInit event.

sc_select_where(add) = " START WITH ittrobjscode = ‘".[glo_ittrobjscode]."’ CONNECT BY PRIOR ittrobjscode = ittrobjpscode ";

:slight_smile: Thank you for the help.