Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2009
    Posts
    34

    Unanswered: Insert into table2 from table1, table3 where

    I have a scenario where i have to insert into table 3
    selecting data from table1, table2 with some conditions





    INSERT INTO TABLE2
    values (IMC_KEY_NO = (SELECT DWT00002_IMC_DMS_MAIN.IMC_KEY_NO FROM DWSODS01.DWT00002_IMC_DMS_MAIN WHERE
    DWT00002_IMC_DMS_MAIN.IMC_KEY_NO = DWT00102_IMC_DERV_MISC.IMC_KEY_NO ),
    FIRST_SPONSOR_DT_KEY_NO = (SELECT DWT00002_IMC_DMS_MAIN.INMKT_FIRST_SPON_DT_KEY_NO
    FROM DWSODS01.DWT00002_IMC_DMS_MAIN
    WHERE DWT00102_DERV_IMC_MISC.LAST_SPON_DT_KEY_NO <> DWT00002_IMC_DMS_MAIN.INMKT_LAST_SPON_DT_KEY_NO AND
    DWT00002_IMC_DMS_MAIN.IMC_KEY_NO = DWT00102_IMC_DERV_MISC.IMC_KEY_NO),
    LAST_SPONSOR_DT_KEY_NO = (SELECT DWSODS01.DWT00002_IMC_DMS_MAIN.INMKT_LAST_SPON_DT_ KEY_NO,
    FROM DWSODS01.DWT00002_IMC_DMS_MAIN
    WHERE
    DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO <> DWT00002_IMC_DMS_MAIN.INMKT_FIRST_SPON_DT_KEY_NO AND
    DWT00002_IMC_DMS_MAIN.IMC_KEY_NO = DWT00102_IMC_DERV_MISC.IMC_KEY_NO))


    I am getting a error message saying "missing comma"
    Can anyone please help
    Last edited by rahulsony111; 11-16-09 at 14:51.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Without DDL for your tables at a minimum, nobody can come close to actually reproducing any error.
    Lack of formatting of code compounds the challenge to discern what is wrong.

    About how many row do you expect/desire will be INSERTed?

    How many rows returned by...
    (SELECT IMC_KEY_NO FROM DWSODS01.DWT00002_IMC_DMS_MAIN WHERE
    DWT00002_IMC_DMS_MAIN.IMC_KEY_NO = DWT00102_IMC_DERV_MISC.IMC_KEY_NO )

    How many rows returned by...
    (SELECT DWT00002_IMC_DMS_MAIN.INMKT_FIRST_SPON_DT_KEY_NO
    FROM DWSODS01.DWT00002_IMC_DMS_MAIN
    WHERE DWT00102_DERV_IMC_MISC.LAST_SPON_DT_KEY_NO <> DWT00002_IMC_DMS_MAIN.INMKT_LAST_SPON_DT_KEY_NO AND
    DWT00002_IMC_DMS_MAIN.IMC_KEY_NO = DWT00102_IMC_DERV_MISC.IMC_KEY_NO)

    How many rows returned by...
    (SELECT DWSODS01.DWT00002_IMC_DMS_MAIN.INMKT_LAST_SPON_DT_ KEY_NO,
    FROM DWSODS01.DWT00002_IMC_DMS_MAIN
    WHERE
    DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO <> DWT00002_IMC_DMS_MAIN.INMKT_FIRST_SPON_DT_KEY_NO AND
    DWT00002_IMC_DMS_MAIN.IMC_KEY_NO = DWT00102_IMC_DERV_MISC.IMC_KEY_NO)
    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 2009
    Posts
    34
    I got what you are saying, but i am unable to think of a solution

    My problem is i have to created a new table with three columns IMC_KEY_NO, FORST_SPONSOR_DT_KEY_NO, LAST_SPONSOR_DT_KEY_NO.
    These columns should be populated from a table1 with some conditions to be satisfied with table2
    My condiitons are
    table1.IMC_KEY_NO = table2.IMC_KEY_NO, then that IMC_KEY_NO should be populated with that common value into new table
    table1.FIRST_SPON_DT_KEY_NO <> table2.INMKT_FIRST_SPON_DT_KEY_NO then that value should be populated for that specific IMC_KEY_NO
    table1.LAST_SPON_DT_KEY_NO <> table2.INMKT_LAST_SPON_DT_KEY_NO then that value should be populated for that specific IMC_KEY_NO


    So how can i modify my code
    Last edited by rahulsony111; 11-16-09 at 15:54.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I got you
    No you did not or you would have answered my questions

    >I got the same error as you said.
    I said nothing about any error

    >So how can i modify my code
    I don't know what you have.
    I don't know what you want.

    You provide us

    No Operating System name or version
    No Oracle version number (SELECT * from v$version).
    No error message (not your interpretation of the actual full and complete message).

    No DDL for your table(s)
    No DML for test data
    No expected/desired results
    Therefore no assistance for you is possible at this time.

    Have you ever successfully composed any INSERT statement which included SELECT .... ?
    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.

  5. #5
    Join Date
    Nov 2009
    Posts
    34
    I am new to oracle (novice)
    the error i got was 'a sub-query is returing more than one rwo into single row'
    i gave this time some more information about my requirements

    Operating system - WIn XP
    oracle 10g

    i didn't get what is DDL and DML
    according to requirements i should populate the new table

  6. #6
    Join Date
    Nov 2009
    Posts
    34
    What about this code

    INSERT INTO TABLE2 (IMC_NO, FIRST_SPONSOR_KEY_NO, LAST_SPONSOR_KEY_NO)
    SELECT DWT00002_IMC_DMS_MAIN.IMC_KEY_NO, DWT00002_IMC_DMS_MAIN.INMKT_FIRST_SPON_DT_KEY_NO, DWT00002_IMC_DMS_MAIN.INMKT_LAST_SPON_DT_KEY_NO
    FROM DWSODS01.DWT00002_IMC_DMS_MAIN, DWSODS01.DWT00102_DERV_IMC_MISC
    WHERE
    (DWT00102_DERV_IMC_MISC.LAST_SPON_DT_KEY_NO <> DWT00002_IMC_DMS_MAIN.INMKT_LAST_SPON_DT_KEY_NO
    OR DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO <> DWT00002_IMC_DMS_MAIN.INMKT_FIRST_SPON_DT_KEY_NO )AND
    DWT00002_IMC_DMS_MAIN.IMC_KEY_NO = DWT00102_DERV_IMC_MISC.IMC_KEY_NO

    Do you think it will work

  7. #7
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Well comment out the insert line and see if the select returns what you are looking for. Also, that will work the first time, how do you plan on maintaining it as the other tables change?

  8. #8
    Join Date
    Nov 2009
    Posts
    34
    Quote Originally Posted by artacus72 View Post
    Well comment out the insert line and see if the select returns what you are looking for. Also, that will work the first time, how do you plan on maintaining it as the other tables change?
    I got what you said and it worked out
    Thanks

Posting Permissions

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