Re: Multitable Insert Problem

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 23 Sep 2006 08:20:54 +0200
Message-ID: <4514d249$0$20242$636a55ce@news.free.fr>

"Dereck L. Dietz" <dietzdl_at_ameritech.net> a écrit dans le message de news: Sn_Qg.7324$Ij.1454_at_newssvr14.news.prodigy.com...
|I was trying to see if I could set up a multitable insert but kept getting
| errors. I eventually got the insert to the bare bones as I only wanted to
| have it work. It never did. I've never used a multitable insert so please
| excuse me if the problem is something very simple - I just can't see what's
| wrong right now.
|
| I was attempting the following insert while running under Oracle 10g R2. I
| was calling this from either a script file and one time put it in an
| SQL*Plus window and received the same error both ways.
|
| The Insert:
|
| INSERT ALL
|
| INTO hmp_well_visits VALUES ( person_id )
|
|
| INTO hmp_well_visits VALUES ( person_id )
|
|
| SELECT DISTINCT a.person_id AS person_id,
| a.mbr_key AS mbr_key,
| a.mbr_sys_key AS mbr_sys_key,
| a.clm_id AS clm_id,
| a.clm_line_nbr AS clm_line_mbr,
| a.clmdtl_key AS clmdtl_key,
| a.srv_dt_from AS srv_dt_from,
| a.cpt_hcpcs AS cpt_hcpcs,
| a.proc_i_1 AS proc_i_1,
| a.dtl_diag AS dtl_diag,
| a.srv_prv_id AS srv_prv_id,
| a.clm_src AS clm_src,
| a.hcfa_pos AS hcfa_pos,
| 'WV' AS hmp_disease_code,
| '1' AS data_source
| FROM hb_hmp_vclaim a
| WHERE a.srv_dt_from >= TO_DATE('&1','MMDDYYYY')
| AND a.person_id > 0;
|
| The error I kept getting:
|
| SP2-0734: unknown command beginning "INTO hmp_w..." - rest of line ignored.
| SP2-0734: unknown command beginning "INTO hmp_w..." - rest of line ignored.
| old 17: WHERE a.srv_dt_from >= TO_DATE('&1','MMDDYYYY')
| new 17: WHERE a.srv_dt_from >= TO_DATE('08022005','MMDDYYYY')
|
|

Avoid blank lines in SQL*Plus; its meaning is "abort the current statement entry":

SQL> INSERT ALL
  2
SQL> INTO hmp_well_visits VALUES ( person_id ) SP2-0734: unknown command beginning "INTO hmp_w..." - rest of line ignored.

SQL>
SQL>
SQL>     INTO hmp_well_visits VALUES ( person_id )

SP2-0734: unknown command beginning "INTO hmp_w..." - rest of line ignored.

SQL>
SQL>
SQL>   SELECT DISTINCT a.person_id  AS person_id,
  2                    a.mbr_key  AS mbr_key,
  3                    a.mbr_sys_key  AS mbr_sys_key,
  4                    a.clm_id  AS clm_id,
  5                    a.clm_line_nbr AS clm_line_mbr,
  6                    a.clmdtl_key  AS clmdtl_key,
  7                    a.srv_dt_from  AS srv_dt_from,
  8                    a.cpt_hcpcs  AS cpt_hcpcs,
  9                    a.proc_i_1  AS proc_i_1,
 10                    a.dtl_diag  AS dtl_diag,
 11                    a.srv_prv_id  AS srv_prv_id,
 12                    a.clm_src  AS clm_src,
 13                    a.hcfa_pos  AS hcfa_pos,
 14                    'WV'   AS hmp_disease_code,
 15                    '1'   AS data_source
 16               FROM hb_hmp_vclaim a
 17              WHERE a.srv_dt_from >= TO_DATE('&1','MMDDYYYY')
 18                 AND a.person_id > 0;
             FROM hb_hmp_vclaim a
                  *

ERROR at line 16:
ORA-00942: table or view does not exist

Regards
Michel Cadot Received on Sat Sep 23 2006 - 01:20:54 CDT