not in does not work?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: not in does not work?

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    I have this query to check weather the segments of two database reside in same tablespaces in both databases


    select segment_name "trafr_seg", partition_name
    from dba_segments a
    where segment_name in
    (
    select segment_name from dba_segments@trafp b
    where tablespace_name='IDX_128M_2'
    and a.segment_name=b.segment_name
    )
    and tablespace_name='IDX_128M_2'
    /


    Now I want to do the other way round so check which segment in first database does not reside in same tablespaces as those in the second tablespace so simply I changed IN to NOT IN but it gives an SQL error

    ERROR at line 6:
    ORA-00920: invalid relational operator
    ORA-02063: preceding line from TRAFP


    NOT IN cannot be used in a correlated query?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    NOT IN across a database link works for me. (8.1.7.2 on both systems)

    I tried:
    Code:
    select table_name from user_Tables
    where table_name not in (
       select table_name from user_Tables@db1)
    Maybe a syntax error somewhere? Post your query for a second(+) set of eyes to look at it...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    the query is

    select segment_name "trafr_seg", partition_name
    from dba_segments a
    where segment_name not in
    (
    select segment_name from dba_segments@trafp b
    where tablespace_name='INTERCON_ADD_D'
    and a.segment_name=b.segment_name
    )
    and tablespace_name='INTERCON_ADD_D'
    where tablespace_name='INTERCON_ADD_D'
    *
    ERROR at line 6:
    ORA-00920: invalid relational operator
    ORA-02063: preceding line from TRAFP

    is exactly the same as the other one just with an extra NOT before in

    I think it is compianing about the

    where tablespace_name='INTERCON_ADD_D'

    in the subquery


    But I dont see why... I am puzzled


    Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
    on Tru64

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Hmm, works for me.

    Maybe if you try fully qualifying tablespace_name with b.tablespace_name??

    Maybe NOT EXISTS would be different??

    Either way, sounds like a bug...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    tried pu a b but still getting error

    NOT EXISTS wouldnt give the desired the result because I am matching rows not testing for existence right :-?

  6. #6
    Join Date
    Dec 1999
    Location
    Cincinnati, Ohio USA
    Posts
    99
    I copied your query,

    select segment_name "trafr_seg", partition_name
    from dba_segments a
    where segment_name not in
    (
    select segment_name from dba_segments@trafp b
    where tablespace_name='INTERCON_ADD_D'
    and a.segment_name=b.segment_name
    )
    and tablespace_name='INTERCON_ADD_D'

    and the only thing I changed was the dynamic link, to equal one of my own dynamic links. And it runs WITHOUT any errors. What version of the database are you on?
    Doug

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
    on Tru64


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