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

Possibly I'm daft, but don't you need a 'VALUES' at the beginning of line 3 ?

Your old code:

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));


Missing VALUE and wrong number of (. You open 11 times with '(' and you close 10 timeswith ')'.

Guess thats should be:

INSERT INTO medical_visit_tab (mv_id, visit_date, charge, visit_coord_role_cui, problem, solution, person) VALUES
((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));