Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Connecting 2 tables...

    Hi

    Running Oracle 9i.

    I have a table1 which I've been loading data into for the last 6 months. The requirement now is to load all that data into another table2.

    Is there a way that the data in table 1 can appear as if it exists in table 2 (only or too)? or something? because I can start loading data into table2 but I also need the last 6 months data (from table2).

    Regards
    Shajju

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shajju
    The requirement now is to load all that data into another table2.
    Why? It is almost always a bad idea to create multiple tables that hold the same type of data...

    Why can't you just put everything into a single table?
    What problem are you trying to solve?

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Right...

    Basically, I have some reports containing KPIs (which have formulas) which are created from some views which in turn are created from schema a.table a.

    Now there was some new data (counters) which I started loading into schema b. table b.

    Now I need to add some more KPIs to this report (making formulas using the counters in schema a. table a). But the query of the report is already showing KPIs (formulas created using counters from schemab. tableb.)

    I hope I've explained in some detail.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Without showing us the table structure (as a CREATE TABLE) and the view structure (as a CREATE VIEW) nobody will be able to give you a useful answer

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    OK, the table structure is shown below..

    TABLE STRUCTURE

    Code:
    CREATE TABLE CELLSTATS
    (
      BSC              VARCHAR2(20 BYTE),
      DATETIME         DATE,
      PERIOD_DURATION  NUMBER,
      DISNORM          NUMBER,
      DISBQA           NUMBER,
      DISBSS           NUMBER,
    )
    NOLOGGING 
    PCTFREE 60
    TABLESPACE ERI_CELLSTATS_D
    PARTITION BY RANGE (DATETIME)
    (
    PARTITION P19900101 VALUES LESS THAN (TO_DATE('1990-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    );
    
    -- CONSTRAINT
    -- ----------
    ALTER TABLE CELLSTATS ADD (CONSTRAINT CELLSTATS_PK PRIMARY KEY (DATETIME, BSC) USING INDEX LOCAL TABLESPACE ERI_CELL_MAIN_I);
    
    -- GRANT
    -- -----
    GRANT SELECT ON  CELLSTATS TO PUBLIC;
    VIEW STRUCTURE

    Code:
    CREATE OR REPLACE VIEW ERICSSON_BSS2.XX_CELL_MAIN_BH_DY
    (BSC, CELL, DATETIME, PERIOD_DURATION, DISNORM, 
     DISBQA, DISBSS)
    AS 
    SSELECT "BSC","CELL","DATETIME","PERIOD_DURATION","DISNORM","DISBQA","DISBSS"FROM CELLSTATS;

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    And the second table is?

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    Sorry, this is the table I was to move/copy the stats from:

    Code:
    CREATE TABLE NUCELLREL (
    DATETIME	DATE,
    BSC	VARCHAR2(25),
    CELL	VARCHAR2(25),
    TRGCELL	VARCHAR2(25),
    PERIOD_DURATION	NUMBER,
    HOATTSHOULDUTRAN NUMBER,
    URGHOVERUTRAN NUMBER,
    SUCURGHOUTRAN NUMBER,
    HOVERCNTUTRAN NUMBER,
    HOVERSUCUTRAN NUMBER,
    HORTTOCHUTRAN NUMBER,
    HOREQCNTUTRAN NUMBER
    ) 
    NOLOGGING 
    PCTFREE 60
    TABLESPACE ERI_NUCELLREL_D
    PARTITION BY RANGE (DATETIME)
    (
    PARTITION P19900101 VALUES LESS THAN (TO_DATE('1990-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    );
    
    -- CONSTRAINT
    -- ----------
    ALTER TABLE NUCELLREL ADD (CONSTRAINT NUCELLREL_PK PRIMARY KEY(DATETIME,BSC,CELL,TRGCELL) USING INDEX LOCAL TABLESPACE ERI_NUCELLREL_I);
    
    -- GRANT
    -- -----
    GRANT SELECT ON NUCELLREL TO USERS;

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Ok, so the tables have a different structure.

    - Do you want matching rows from CELLSTATS and NUCELLREL? If yes, what is the (primary) key that can be used to identify matching rows.

    - Do you want all rows from both tables with just the corresponding columns?

  9. #9
    Join Date
    Aug 2008
    Posts
    464
    If I understand your question correctly, I want all the columns in the second table structure that I posted to exist in the first table structure and the corresponding data from the 2nd table.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I still don't understand, how should data from columns in NUCELLREL that are not available in CELLSTATS be displayed?

    Maybe if you posted some sample rows from each table and the expected result that you want, things will get clearer.

  11. #11
    Join Date
    Aug 2008
    Posts
    464
    That's what my question was. If the data in NUCELLREL can be transferred or represented as if it belongs to the CELLSTATS table?

    Because at the end of the day, I need to select the counters available in NUCELLREL from the CELLSTATS table (somehow).

    NUCELLREL

    datetime, bsc, cell, counter1, counter2, counter3.

    CELLSTATS
    datetime, bsc, cell, counter4, counter5, counter6

    Silly question is:
    Any way to do select counter1, counter2, counter3 from CELLSTATS?

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shajju View Post
    That's what my question was. If the data in NUCELLREL can be transferred or represented as if it belongs to the CELLSTATS table?
    If there is some kind of relation between the tables (which you still haven't revealed to us) then you can use a join.

    Because at the end of the day, I need to select the counters available in NUCELLREL from the CELLSTATS table (somehow).
    Again: how do these two tables relate to each other? If they have common columns (I suspect datetime, bsc and cell) then use a simple join.

  13. #13
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju View Post
    Because at the end of the day, I need to select the counters available in NUCELLREL from the CELLSTATS table (somehow).

    NUCELLREL

    datetime, bsc, cell, counter1, counter2, counter3.

    CELLSTATS
    datetime, bsc, cell, counter4, counter5, counter6

    Silly question is:
    Any way to do select counter1, counter2, counter3 from CELLSTATS?
    Silly answer: generally no way. How would you relate them, if counter1 would be number of calls abroad and counter4 number of calls to the same network?

    Anyway, there is a possibility that counters are in some relationship. It is data specific, so only the analyst (= the person who took user requirements and designed tables) should be able to tell this. It has nothing to do with Oracle (the way data is stored). Maybe you should ask him/her directly.

Posting Permissions

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