out joint is not working in select statment
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: out joint is not working in select statment

  1. #1
    Join Date
    Sep 2001
    Posts
    52

    Angry

    Hi Guys,

    The following query is working fine in oracle 7.3 and oracle 8i but not working in 9i. Can you tell me why? The outer joint in SELECT statement is not allowed..thats what 9i complaining but it was fine for 8i.

    Thanx in advance
    Devang

    SELECT C.COUNTER_ID,
    C.COUNTER_SUBTYPE_ID,
    NVL(CS.PRINT_SEQ(+),0),
    NVL(CS.COUNTER_SUBTYPE_DESC(+),
    C.COUNTER_ID || ' ' ||
    C.COUNTER_SUBTYPE_ID || ' no description'),
    NVL(CS.PRINT_FORMAT(+),' '),
    SUM(NVL(C.COUNTER_VALUE,0))
    FROM COUNTER_DETAIL C,
    COUNTER_SUBTYPE CS
    WHERE TO_NUMBER(C.COUNTER_OWNER_ID) = 6508
    AND C.COUNTER_ID = CS.COUNTER_ID(+)
    AND C.COUNTER_SUBTYPE_ID = CS.COUNTER_SUBTYPE_ID(+)
    GROUP BY C.COUNTER_ID, C.COUNTER_SUBTYPE_ID, CS.PRINT_SEQ(+),
    CS.COUNTER_SUBTYPE_DESC(+), CS.PRINT_FORMAT(+)
    ORDER BY CS.PRINT_SEQ(+), C.COUNTER_ID, C.COUNTER_SUBTYPE_ID
    /


    [Edited by DevangP on 10-03-2002 at 02:43 PM]

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I get this error in 8.1.7

    SQL> select a.ename, nvl(a.deptno(+), 0), b.deptno
    2 from emp a, dept b
    3 where a.deptno(+) = b.deptno;
    select a.ename, nvl(a.deptno(+), 0), b.deptno
    *
    ERROR at line 1:
    ORA-30563: outer join operator (+) not allowed in select-list

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    (+) only makes sense in the WHERE clause. Remove it from the SELECT, GROUP BY and ORDER BY

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    that's why I wonder why he says it works in 8i, funny

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width