This discussion is archived
1 Reply Latest reply: Oct 8, 2013 1:24 PM by Yuvaraj C. RSS

No result from the query

user4199159 Newbie
Currently Being Moderated

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. Explorer
    Currently Being Moderated

    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points