Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2003
    Location
    Romania
    Posts
    10

    Unanswered: Strange problem with a view....

    I has create a view few_clients in this way:
    “Create or replace view few_clients as
    select * from all_clients where client_id not in
    (select client_id from schema2.all_clients)”

    The problem is when execute the statement select count(*) from few_clients the result is 0!!!!. I’m very sure that the result must be > 0. If I change view definition in this way:
    “Create or replace view few_clients as select * from all_clients where client_id in(select client_id from schema2.all_clients)”
    Then “select count(*) from few_clients” return a corect result.

    Way when I use “in” clause obtain a correct result and when I use “not in” clause the result is always 0???

    OBS: the sub query return results from another schema named “schema2”.

    Thanks a lot!

  2. #2
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello!


    (1) Create or replace view few_clients as
    select * from all_clients
    where client_id not in
    (select client_id from schema2.all_clients);

    (2) Create or replace view few_clients as
    select * from all_clients
    where client_id in
    (select client_id from schema2.all_clients);

    Your (2) is the opposite of your (1).
    So (if i've correct understood) you want to get all values from all_clients
    which are contained in schema2.
    Your (2) do it.
    Your (1) gets all values from all_clients that are not contained in schema2.
    Regards,
    Julia

  3. #3
    Join Date
    Feb 2003
    Location
    Romania
    Posts
    10
    Correct, but the second view don’t return correct records(in fact, returns 0 records). It must return all clients that are not contained in schema2.
    Last edited by Maf; 03-30-04 at 07:27.

  4. #4
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    I tested it on my DB --> the statement (1) does what you want to achieve.
    Maybe 'schema2' makes problems?
    Last edited by julla27; 03-30-04 at 07:41.
    Regards,
    Julia

  5. #5
    Join Date
    Feb 2003
    Location
    Romania
    Posts
    10
    Thanks julla27 for answers, the problem was because the subquery may contain a "Null" value. If the subquery contains a Null value then the query does not return a value when it is used in a "Not in" clause.
    The solution is:
    Create or replace view few_clients as
    select * from all_clients
    where client_id not in
    (select nvl(client_id,-1) from schema2.all_clients);
    Last edited by Maf; 03-30-04 at 07:57.

  6. #6
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    OK,
    my test-view doesn't contain any NULL-values!

    Fine!
    Regards,
    Julia

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Just asking ... would something like this work, Maf?
    PHP Code:
    CREATE OR REPLACE VIEW few_clients
    AS
       
    SELECT *
         
    FROM all_clients ac1
        WHERE ac1
    .client_id NOT IN (SELECT ac2.client_id
                                      FROM schema2
    .all_clients ac2
                                     WHERE ac1
    .client_id ac2.client_id); 

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    you could try this which might also be more efficient

    select a.*
    from all_clients a, schema2.all_clients b
    where a.client_id = b.client_id(+)
    and b.client_id is null

  9. #9
    Join Date
    Mar 2004
    Posts
    10
    If you are using CBO, can you try with _unnest_subquery=false in init.ora?
    You can also try setting it as :
    alter session set "_unnest_subquery=false"
    Just curious.
    Sudip Datta
    Server Technologies
    Oracle Corporation

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >It must return all clients that are not contained in schema2.
    Would this work for you?
    select * from all_clients
    minus
    select * from schema2.all_clients;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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