1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

No result from the query

Discussion in 'Oracle Apps Technical' started by mikerataski, Oct 8, 2013.

  1. mikerataski

    mikerataski Guest

    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, 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;
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,
    Try the following query.

    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;

    Hope this will works. because the red colours are mistakes.

    Thanks&Regards,
    Jagadekara Reddy