inserts into join tables

[Bob Kimmel]

Im having problem getting the syntax
right to insert into join tables. I
can view the in in a select so the joins are
good.
This is how I thought is should look
like:

  
SQL> insert into CCEVS_PROJECTS_REGISTRY
  2  (test_this_name)
  3   select CCEVS_PROJECTS_REGISTRY.PRODUCT_NAME
  4    FROM (CCEVS_PROJECTS_REGISTRY INNER JOIN (TECHNOLOGY_TYPES
  5    INNER JOIN CCEVS_TECHNOLOGY_REGISTRY ON TECHNOLOGY_TYPES.TECHNOLOGY_ID
  6    = CCEVS_TECHNOLOGY_REGISTRY.TECHNOLOGY_ID) on
  7    CCEVS_PROJECTS_REGISTRY.PROJECT_ID = CCEVS_TECHNOLOGY_REGISTRY.PROJECT_ID)
  8    INNER JOIN (PP_REGISTRY INNER JOIN CCEVS_PP_ASSIGNED_REGISTRY
  9    ON PP_REGISTRY.PP_ID = CCEVS_PP_ASSIGNED_REGISTRY.PP_ID)
 10    ON CCEVS_PROJECTS_REGISTRY.PROJECT_ID = CCEVS_PP_ASSIGNED_REGISTRY.PROJECT_ID;
(test_this_name)
 *
ERROR at line 2:
ORA-00904: invalid column name
While playing with it I did this.  It says
it add some rows but I figure out what or
where:

SQL> insert into CCEVS_PROJECTS_REGISTRY
  2  (CCEVS_PROJECTS_REGISTRY.PRODUCT_NAME)
  3   select CCEVS_PROJECTS_REGISTRY.PRODUCT_NAME
  4    FROM (CCEVS_PROJECTS_REGISTRY INNER JOIN (TECHNOLOGY_TYPES
  5    INNER JOIN CCEVS_TECHNOLOGY_REGISTRY ON TECHNOLOGY_TYPES.TECHNOLOGY_ID
  6    = CCEVS_TECHNOLOGY_REGISTRY.TECHNOLOGY_ID) on
  7    CCEVS_PROJECTS_REGISTRY.PROJECT_ID = CCEVS_TECHNOLOGY_REGISTRY.PROJECT_ID)
  8    INNER JOIN (PP_REGISTRY INNER JOIN CCEVS_PP_ASSIGNED_REGISTRY
  9    ON PP_REGISTRY.PP_ID = CCEVS_PP_ASSIGNED_REGISTRY.PP_ID)
 10    ON CCEVS_PROJECTS_REGISTRY.PROJECT_ID = CCEVS_PP_ASSIGNED_REGISTRY.PROJECT_ID;
10 rows created.

Thanks,
Bob

[Mark Spritzler]

Bob, I don't quite understand your question.
If you are wondering why the first one gives you an error? It is because "(test_this_name)" is not a column in your table you are inserting to. So of course it will give you an invalid column error.
Mark

[Bob Kimmel]

What I was trying to do is add a new row a data using the insert
and select statement. I have three tables that have like primary keys
and I joined them together hoping to be able to add data with one insert
statement. After reading some more this weekend, I dont think I can do it this way, unless you know of some trick. Next Im going to make a view of the three table and try to insert into the view. Any other suggestions?
I'm fairly new so be nice:-)
Thanks,
Bob

[Mark Spritzler]

Well, I am still confused at what you are doing. If you have one table you are inserting into, then what you want to do is possible. I have many insert statements that are records inserted from a Select statement. Just like thus

INSERT /* +APPEND */ INTO insertTable
SELECT <field-list-here>
  FROM tablea, tableb, tablec
 WHERE tablea joins to tableb
   AND tableb joins to tablec  

My joins are in a where clause, but they can use the ANSI join standards codes. Anything you want.
Also in my example the resultset structure matches the structure of the insertTable.
Hope that helps.
Mark