1 Reply Latest reply: Oct 8, 2013 3:24 PM by Yuvaraj C. RSS

    No result from the query

    user4199159

      Hi.. , Thanks for your quick reply ....

       

       

      My requirememnt is to fetch the 'ORIG_SYSTEM' from hz_orig_sys_references table based on the ownere_table_id paramenter passed to it . There may be multiple 'ORIG_SYSTEM' for one owner_table_id but i want to display all 'ORIG_SYSTEM' of each owner_table_id in a single row sepreted by '_' .Please suggest any effective solution for this .

       

       

      Desired Output .

       

       

      'ORIG_SYSTEM'  OWNER_TABLE_ID

      AXIS-QUT-OBI     1056

      ZST-FBS-TIU       2309

       

       

       

      Hi , I am using the following query to get data as mentioned in the query .. this doesn't return me result .... please suggest

       

       

       

        SELECT OWNER_TABLE_ID,substr(SYS_CONNECT_BY_PATH(ORIG_SYSTEM , '-'),2) SRC

                FROM   (   select OWNER_TABLE_ID, ORIG_SYSTEM,

                             count(*) OVER ( partition by OWNER_TABLE_ID) cnt,

                          ROW_NUMBER () OVER ( partition by OWNER_TABLE_ID order by ORIG_SYSTEM) seq

                          from hz_orig_sys_references

            where seq=cnt

          start with

         seq=1

          connect by prior

         seq+1=seq

      and prior

         OWNER_TABLE_ID=OWNER_TABLE_ID;

       

       

       

      When i apply the same logic in the below query to get the data ename of its respective depet it returns me properly but why doesn't it return for the above qry .

       

       

       

      select

         deptno,

         substr(SYS_CONNECT_BY_PATH(ename, '-'),2) name_list

      from

         (

         select

           ename,

           deptno,

           count(*) OVER ( partition by deptno ) cnt,

           ROW_NUMBER () OVER ( partition by deptno order by ename) seq

         from

           scott.emp

         where

           deptno is not null)

      where

         seq=cnt

      start with

         seq=1

      connect by prior

         seq+1=seq

      and prior

         deptno=deptno

         and deptno=30;

        • 1. Re: No result from the query
          Yuvaraj C.

          Please try this now:

           

          SELECT OWNER_TABLE_ID,substr(SYS_CONNECT_BY_PATH(ORIG_SYSTEM , '-'),2) SRC

          FROM   ( 

          select OWNER_TABLE_ID, ORIG_SYSTEM,

                  count(*) OVER ( partition by OWNER_TABLE_ID) cnt,

                  ROW_NUMBER () OVER ( partition by OWNER_TABLE_ID order by ORIG_SYSTEM) seq

          from hz_orig_sys_references)

          where seq=cnt

          start with

          seq=1

          connect by prior

          seq+1=seq

          and prior

          OWNER_TABLE_ID=OWNER_TABLE_ID;