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

    Unanswered: Making a column.....

    Hi guys

    Using Oracle 9i.

    I was hoping someone could advise on this. Is it possible to make a column in one schema1.table1 appear in another schema2.table2? I know I can create a view in the second schema to show that schema1.table1.column1 by simply selecting the column from schema1.
    But is it possible to make column1 appear in an already existing view in schema2?

    I'm using views created in schema2 from schema1.table1 and schema2.table2.

    SCHEMA1.TABLE 1
    Code:
    CREATE TABLE TABLE1(
      BSC              VARCHAR2(20 BYTE),
      CELL             VARCHAR2(20 BYTE),
      DATETIME       DATE,
      COL3              NUMBER,
      COL4              NUMBER,
      COL5              NUMBER)
    SCHEMA2.TABLE 2
    Code:
    CREATE TABLE TABLE2(
      DATETIME       DATE,
      BSC               VARCHAR2(25 BYTE),
      CELL              VARCHAR2(25 BYTE),
      COL3             NUMBER,
      COL4             NUMBER)
    Is it possible to make COL5 appear in SCHEMA2.TABLE2?

    Regards

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Using Oracle 9i.
    obsoleted & unsupported.
    Last century.
    Sorry!
    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
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shajju View Post
    Is it possible to make COL5 appear in SCHEMA2.TABLE2?
    Only through a view

  4. #4
    Join Date
    Aug 2008
    Posts
    464

    view

    OK, thanks. but can I make the column in schema1.table1 a part of schema2.table2 using a view? If so, would you mind helping me out here?

    No other way to link schema1.table1.column1 to schema2.table2?

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju View Post
    I was hoping someone could advise on this. Is it possible to make a column in one schema1.table1 appear in another schema2.table2? I know I can create a view in the second schema to show that schema1.table1.column1 by simply selecting the column from schema1.
    But is it possible to make column1 appear in an already existing view in schema2?
    I have no idea, what you want to achieve. The only way to put column into table is to add it.
    Quote Originally Posted by shajju View Post
    I'm using views created in schema2 from schema1.table1 and schema2.table2.

    SCHEMA1.TABLE 1
    Code:
    CREATE TABLE TABLE1(
      BSC              VARCHAR2(20 BYTE),
      CELL             VARCHAR2(20 BYTE),
      DATETIME       DATE,
      COL3              NUMBER,
      COL4              NUMBER,
      COL5              NUMBER)
    SCHEMA2.TABLE 2
    Code:
    CREATE TABLE TABLE2(
      DATETIME       DATE,
      BSC               VARCHAR2(25 BYTE),
      CELL              VARCHAR2(25 BYTE),
      COL3             NUMBER,
      COL4             NUMBER)
    Is it possible to make COL5 appear in SCHEMA2.TABLE2?
    Logical question: what should be the value of COL5 for rows in SCHEMA2.TABLE2? As you are talking about view, you may put there any constant/expression from base table(s). E.g.
    Code:
    CREATE VIEW VIEW1 AS
    SELECT datetime, bsc, cell, col3, col4, col5
    FROM schema1.table1
    UNION ALL
    SELECT datetime, bsc, cell, col3, col4, <any expression you want> col5
    FROM schema2.table2
    Without specifying the exact definition of those views, there is not much to add.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shajju View Post
    OK, thanks. but can I make the column in schema1.table1 a part of schema2.table2 using a view?
    No, you cannot.
    You can create a VIEW in schema2 that includes the columns from table2 and col5 from table1.

    If e.g. BSC, CELL and datetime are the primary key/foreign key of those tables, then you could do the following:
    Code:
    CREATE VIEW schema2.view2
    (DATETIME, BSC, CELL, COL3, COL4, COL5)
    AS
    SELECT t2.datetime, 
           t2.bsc, 
           t2.cell, 
           t2.col3,
           t2.col4
           t1.col5
    FROM schema2.table2 t2 
      JOIN schema1.table1 ON t2.datetime = t1.datetime
                         AND t2.bsc = t1.bsc
                         AND t2.cell = t1.bsc

  7. #7
    Join Date
    Aug 2008
    Posts
    464

    Thanks

    Hey thanks guys....Help really appreciated. Your suggestion has done the job

  8. #8
    Join Date
    Aug 2008
    Posts
    464

    but....

    Hi again guys

    Small complication....Although the datetime column is a PK, I can't join on datetime because the datetime columns in both tables contain different values.

    Any way out?

    Regards

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shajju View Post
    Although the datetime column is a PK, I can't join on datetime because the datetime columns in both tables contain different values.

    Any way out?
    If you can't join those two tables, then there is no way out.

  10. #10
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju View Post
    Hi again guys

    Small complication....Although the datetime column is a PK, I can't join on datetime because the datetime columns in both tables contain different values.

    Any way out?

    Regards
    Take one step back. Have a look at stored data. Think about all its possible combination that may occur. Are you able to transfer it to the (one and only) required result? If so, just write the rules in SQL. If no, how would you expect Oracle to do it?

  11. #11
    Join Date
    Aug 2008
    Posts
    464
    Hi

    I can join the two tables but just not on datetime. But when I join on BSC, CELL the result contains far too many rows.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shajju View Post
    I can join the two tables but just not on datetime. But when I join on BSC, CELL the result contains far too many rows.
    If you cannot (uniquely) relate one row from table1 to one row in table2 then apparently those two tables do not contain the same information.

    You will need to re-think your design

  13. #13
    Join Date
    Aug 2009
    Posts
    262
    it appears the datetime column is not an important column in your desired view . i assume this from your trying of come around this problem .

    extract year from the date time , ( or even months) while creating the view and you will get your matching rows .

  14. #14
    Join Date
    Aug 2008
    Posts
    464
    You're right. I'm happy to use the datetime of schema1.table1 and ignore the datetime in schema2.table2. Is this possible?

    If so, could you please give me an example? Thanks

    Regards

  15. #15
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju View Post
    I can join the two tables but just not on datetime. But when I join on BSC, CELL the result contains far too many rows.
    When joining two tables on columns with duplicate values in both tables, the result set contains all their combinations. E.g. with 4 rows in TABLE1 with (BSC, CELL) = (1, 1) and 3 rows with (BSC, CELL) = (1, 1) in TABLE2, the resultset will contain 12 rows.

    Just curious, are you able to derive the desired result from given content of TABLE1 and TABLE2? E.g. what shall be the result, when TABLE1 contains
    Code:
    BSC CELL DATETIME OTHER COLUMNS
    -------------------------------
    1   1    20091201 <some table1 values>
    1   1    20091204 <some table1 values>
    1   1    20091205 <some table1 values>
    1   1    20091206 <some table1 values>
    and TABLE2 contains
    Code:
    BSC CELL DATETIME OTHER COLUMNS
    -------------------------------
    1   1    20091201 <some table2 values>
    1   1    20091202 <some table2 values>
    1   1    20091203 <some table2 values>
    ? Of course, this example may not represent your real data; but as you did not post it, this is the best I could construct.
    Anyway, the concept is not affected by this, as nobody is able to write the query without identifying the correct result set (and rules leading to it) from source data.

Posting Permissions

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