Need help with complicated INSERT query!

Hi guys,

We're having trouble inserting into a table, using data from another table. We are using an Object-Relational DBS. The new table includes a REF, and two nested tables. the latest query that I have tried is:

INSERT INTO medical_visit_tab(mv_id, visit_date, charge, visit_coord_role_cui, problem, solution, person)
(SELECT ID, VISIT_DATE, CHARGE, VISIT_COORDINATOR_ROLE_CUI
FROM oraheal.medical_visit
WHERE p_chronic_cui is NULL
AND s_therapy_cui is NULL),
medicalProb_tableType((SELECT medicalProbType(p_problem_cui,p_acute_cui)
FROM oraheal.medical_visit
WHERE p_chronic_cui is NULL
AND s_therapy_cui is NULL),
medicalSolu_tableType((SELECT medicalSolutionType(s_carer_id, s_drug_cui, s_drug_cost, s_drug_targetchemical_cui1, s_drug_targetchemical_cui2)
FROM oraheal.medical_visit
WHERE s_therapy_cui is NULL
AND p_chronic_cui is NULL),
(SELECT TREAT(REF(a) as REF patientType)
FROM patient_tab a, oraheal.medical_visit o
WHERE a.name = o.name));

The error this gives in Oracle 9i is:

ERROR at line 5:
ORA-00933: SQL command not properly ended

line 5 is -->> AND s_therapy_cui is NULL),

The first SELECT is for 4 simple attributes, the second and third SELECT for the Nested Tables (called problem and solution), and the last SELECT is for the REF (person).

Is it wrong to use more than one SELECT statement when inserting into a new table? Or is the syntax that we are using at the moment wrong?

If anyone has any clue could you pleaaase reply.....have been stuck on this for hours!

Cheers

That syntax looks completely wrong to me, now it's possible they changed it between 8 and 9 but I doubt it, I am used to seeing something like this:



Maybe if you provide the table structures and an explanation of what you are trying to do it would help.

The only tip I can offer is that the select statement has to run on it's own, start with that.