Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2009
    Posts
    55

    Unanswered: join two table in two different instance

    Hi,
    I have two table in different instance .

    IMEI in instance A

    RCA_SMART_CARD in instance B

    Below is the desc table :
    SQL> desc RCA_SMART_CARD;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    N_CARD_ID NOT NULL NUMBER(10)
    C_CARD_SERIAL_NUMBER NOT NULL VARCHAR2(20)
    C_SIM_MSISDN VARCHAR2(20)
    C_SIM_IMSI VARCHAR2(20)
    C_LINKED_CARD VARCHAR2(20)
    N_PRO_IDENTIFIER NOT NULL NUMBER(4)
    C_CARD_TYPE VARCHAR2(1)
    N_SIM_STATE NUMBER(1)
    N_EEPROM_SPACE_LEFT NUMBER(9)
    N_VOLATILE_SPACE_LEFT NUMBER(9)
    N_NONVOLATILE_SPACE_LEFT NUMBER(9)
    N_CARD_OPTI NOT NULL NUMBER(15)
    N_PRODUCT_ID NUMBER(10)
    D_CREATION_DATE DATE
    D_MODIFICATION_DATE DATE
    D_STATUS_MODIFICATION_DATE DATE

    SQL> desc IMEI;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    MSISDN NOT NULL VARCHAR2(20)
    IMEI NOT NULL VARCHAR2(16)
    DATE_MOD NUMBER(13)
    IMSI VARCHAR2(18)
    ICCID VARCHAR2(20)
    T_PROF RAW(20)
    EXTRA_DATA VARCHAR2(100)


    If I want to join two table together .
    I want to search the number of record in IMEI that have N_SIM_STATE =1 in RCA_SMART_CARD .
    The MSISDN in IMEI is equal to C_SIM_MSISDN in RCA_SMART_CARD .
    How can I do and what is the sql statment ??
    Please advice .

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select count(*)
    from IMEI
    where MSISDN in
    (SELECT C_SIM_MSISDN FROM RCA_SMART_CARD WHERE N_SIM_STATE =1)
    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.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What is an "instance" (in your case)? Is it just another table in the same schema (i.e. both tables owned by the same user)? Is it another schema in the same database? Different databases?

    Maybe a database link is what you'll need to use.

  4. #4
    Join Date
    Aug 2009
    Posts
    55
    Quote Originally Posted by anacedent View Post
    select count(*)
    from IMEI
    where MSISDN in
    (SELECT C_SIM_MSISDN FROM RCA_SMART_CARD WHERE N_SIM_STATE =1)
    Hi,
    The table is stored in different database .
    One is database/instance A
    The second one is in database/instance B .

    If you type ps -fe |grep pmon
    you can see process pmon_A , pmon_B .

    Please advice ....

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    So it is a database link, then.

  6. #6
    Join Date
    Aug 2009
    Posts
    55
    Quote Originally Posted by anacedent View Post
    select count(*)
    from IMEI
    where MSISDN in
    (SELECT C_SIM_MSISDN FROM RCA_SMART_CARD WHERE N_SIM_STATE =1)
    Hi,
    if the field MSISDN have many repeat in IMEI ,
    I just want to have count unique MSISDN ( only count one time when it appear, do not repeat) , how can I modify this sql statement ???
    Pleas advice .

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
    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.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select MSISDN,count(*)
    from IMEI
    where MSISDN in
    (SELECT C_SIM_MSISDN FROM RCA_SMART_CARD WHERE N_SIM_STATE =1)
    group by MSISDN
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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