Announcement

Collapse
No announcement yet.

Hierarchical data by Oracle

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Hierarchical data by Oracle

    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

    Code:
    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.

    Comment


    • #3
      I can't use view, because the condition for
      START WITH ittrobjscode = '001'

      is intended to be filled with global variable
      START WITH ittrobjscode = '[glo_ittrobjscode]'

      Well, I finally use some work around. It's messy, but at least shows the right values.
      I firstly use standard query without START WITH and CONNECT BY, generate it. Then add the START WITH and CONNECT BY later.
      The grid fields and search fields will be reset. And it looks like this pic (attached) in Edit Fields menu.

      Comment


      • #4
        I tried to make a view as rr suggested. And it turns out it worked.
        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 ";

        Thank you for the help.

        Comment

        Working...
        X