0 Replies Latest reply: Jan 7, 2013 11:25 PM by 822071 RSS

    Default Order by in ODI Interface

    822071
      Hi,

      my question is how to disable any order by statement from ODI ? my problem is that i have build a hierarchy data using CONNECT BY ,START WITH. i dont want ODI to sort data while writing to an XML file, but no matter what i do the data is getting sorted.

      query in sql is returning right result but when i generate a file using interface the order gets all flipped out. plus the actual query of CONNECT by is in a view and all i am doing is using a table MODEL so it shouldn’t make a difference. I think ODI dose some kind of sorting by default. let me show you some snippets of my code:

      let me show you some snippets of my code:

      CREATE OR REPLACE FORCE VIEW CAS_DATA.EDOC360_ORG_H
      (
      EMP_ASG,
      EMP_ORG,
      SUP_ORG,
      SUP_ASG
      )
      AS
      SELECT emp_asg,
      emp_org,
      sup_org,
      sup_asg
      FROM edoc360Org emp
      START WITH emp.sup_asg IS NULL
      CONNECT BY NOCYCLE PRIOR emp.emp_asg = emp.sup_asg
      ORDER SIBLINGS BY emp.sup_asg;

      this is the view and in sql the records it is returning are these:

      un.FJ.KS00 KS00 Konsernsjef – CEO (un.FJ.KS00)
      un.FJ.DR00 DR00 Group Industrial Development (un.FJ.DR00) KS00 Konsernsjef – CEO (un.FJ.KS00) un.FJ.KS00
      un.FJ.DR01 DR01 Group Industrial Program & OE (un.FJ.DR01) DR00 Group Industrial Development (un.FJ.DR00) un.FJ.DR00
      un.FJ.DR58 DR58 Project Management (un.FJ.DR58) DR01 Group Industrial Program & OE (un.FJ.DR01) un.FJ.DR01

      but after using the interface to write these records to XML file i am getting this:

      KS00 Konsernsjef – CEO (un.FJ.KS00)
      3160.100 Business Transformation, Aalborg (un.SN.3160.100)
      3180 Programme Office (un.SN.3180)
      3190 Strategi Telenor DK (un.SN.3190)

      First record line is correct but after that records are sorted by ODI.

      Please help.

      Regards
      Atif Dar