Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Question Unanswered: comparing tables from different schemas

    hi guys,
    im a newbie, i have a question.if i need to compare or to query tables from two different schemas. what are the ways to do it .
    I wrote a query from schema1 like
    SELECT COUNT(schema1.DEPARTMENT.DEP)
    FROM schema1.DEPARTMENT
    WHERE schema1.DEPARTMENT.DEPCODE = schema2.DEPARTMENT_V.CODE;
    i had user privileges on both the schema
    but got error as invalid identifier. DO i need to have more privileges or if i am doing it wrong, kindly suggest me the right way to do this.

    thanks,
    Dave

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    SELECT COUNT(d1.DEP)
    FROM DEPARTMENT d1, Schema2.department_v d2
    WHERE d1..DEPCODE = d2.CODE;

    You have to have a SELECT privilege ON Schema2.department_v table. and only owner of this table can grant this. No need to mention schema1 in this statement because you connect as schema1.

    HTH
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151
    User system can grant almost all privileges on the database in spite of he is not owner of objects belonged to another schema.
    Joel Pérez

  4. #4
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151
    I think this example can help you:

    SQL> show user
    USER is "SYSTEM"
    SQL>
    SQL> create user u1 identified by u1;

    User created.

    SQL> ed
    Wrote file afiedt.buf

    1* create user u2 identified by u2
    SQL> /

    User created.

    SQL> grant connect, resource to u1,u2;

    Grant succeeded.

    SQL> conn u1/u1@ldevcl2
    Connected.
    SQL>
    SQL> create table t1u1 ( c1 number);

    Table created.

    SQL> insert into t1u1 values (10);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> conn u2/u2@ldevcl2
    SQL>
    SQL> create table t1u2 ( c2 number );

    Table created.

    SQL> insert into t1u2 values (20);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> conn u1/u1@ldevcl2
    Connected.
    SQL>
    SQL> show user
    USER is "U1"
    SQL>
    SQL> grant select on t1u1 to u2;

    Grant succeeded.

    SQL> conn u2/u2@ldevcl2
    Connected.
    SQL>

    SQL> desc t1u2
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    C2 NUMBER

    SQL>
    SQL> desc u1.t1u1
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    C1 NUMBER

    SQL>
    SQL> select u1.t1u1.c1 from u1.t1u1;

    C1
    ----------
    10


    SQL> ed
    Wrote file afiedt.buf

    1* select u1.t1u1.c1, t1u2.c2 from u1.t1u1, t1u2
    SQL> /

    C1 C2
    ---------- ----------
    10 20

    SQL>
    Joel Pérez

  5. #5
    Join Date
    Jan 2004
    Posts
    2

    thanks

    Thanks guys, i granted a select on the tables for the other user/schema ...It worked.. Thanks again ..
    Dave

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Below is an example of poor/inefficient SQL coding:
    SELECT COUNT(d1.DEP)
    FROM DEPARTMENT d1, Schema2.department_v d2
    WHERE d1.DEPCODE = d2.CODE;


    Tables which are in the FROM clause should contribute columns in the SELECT clause.
    Since "Schema2.department_v d2" does not return any data,
    it does not belong in the FROM clause.
    A better way of coding is:

    SELECT COUNT(d1.DEP)
    FROM DEPARTMENT d1
    where exists ( SELECT '1'
    FROM Schema2.department_v d2
    WHERE d1.DEPCODE = d2.CODE
    );
    In the vast majority of cases the rewritten SQL will
    perform better ( or at least as well) as the original code.

Posting Permissions

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