Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Australia
    Posts
    2

    Arrow Unanswered: Please help with INSERT statement!

    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

  2. #2
    Join Date
    Oct 2003
    Posts
    16

    Question Maybe i'm daft

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

  3. #3
    Join Date
    Sep 2003
    Location
    Brussel
    Posts
    52

    Re: Please help with INSERT statement!

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

Posting Permissions

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