Home » SQL & PL/SQL » SQL & PL/SQL » Loading missing data from one table to another
Loading missing data from one table to another [message #238887] Sun, 20 May 2007 17:15 Go to next message
concorde800
Messages: 52
Registered: May 2007
Member
I have a scenario where I want to create a (record set) or a table from 2 tables.

Table A has records that are missing for a date range (1st to 8th May)
Table B has different set record set, but has the missing ones from Table A.

These missing ones from Table B need to be populated back into Table A. In this process, I must take care not to overwrite records in Table A that have been updated with a different record_change_date greater than May 8th.

My structure is like this. Am I missing something?? I am running ORACLE 9i.

CREATE TABLE TEMP_TABLE1 
AS
SELECT a.col1, a.col2, a.col3
  FROM TABLE1 a, Table2 b
  where a.record_change_date between ('01-May-2007') and ('08-May-2007')
    and where b.column1 NOT IN (Select a.column1 from Table B) 
Re: Loading missing data from one table to another [message #238888 is a reply to message #238887] Sun, 20 May 2007 17:24 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
select '01-May-2007 this is NOT date datatype. This is a simple string' from dual;
TO_DATE should ALWAYS be used to convert string to date datatype.
Re: Loading missing data from one table to another [message #238889 is a reply to message #238888] Sun, 20 May 2007 17:27 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
The column record_change_date is common to both tables and is of the datatype DATE, so won't the string comparison work??
Re: Loading missing data from one table to another [message #238890 is a reply to message #238889] Sun, 20 May 2007 17:29 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
In such scenarios is a join (outer-join) between two tables needed?
Re: Loading missing data from one table to another [message #238891 is a reply to message #238887] Sun, 20 May 2007 17:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
CREATE TABLE TEMP_TABLE1 
AS
SELECT a.col1, a.col2, a.col3
  FROM TABLE1 a, Table2 b
  where a.record_change_date between ('01-May-2007') and ('08-May-2007')
    and where b.column1 NOT IN (Select a.column1 from Table B)

I suspect that the statement above is invalid & will throw an error.

Why is Table2 part of the topmost FROM clause when NO data is being selected from it?
Table2 can & should be eliminated out of the topmost FROM clause.
You still have not addressed the issue of using strings where DATE datatype is (more) correct.
Re: Loading missing data from one table to another [message #238899 is a reply to message #238891] Sun, 20 May 2007 20:40 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Syntax-wise its correct. I'am trying to put the complete picture here. Table A is the one with missing records for a date range. Table B has "that missing info" which has to be loaded into TABLE A.

My final query is this. Is there some thing incorrect on this? Date comparisons have been handled and they work.

SELECT /*+ PARALLEL (A,6) PARALLEL (B,6) */
distinct a.key_invc_fact_cnct_id, a.key_invc_stat_dim_cnct_id,
       a.key_plnt_loctn_dim_cnct_id, a.key_itm_cat_dim_cnct_id,
       a.key_invc_doc_type_dim_cnct_id, a.key_invc_type_dim_cnct_id,
       a.key_crncy_dim_cnct_id, a.key_invc_date_dim_id,
       a.dlvry_doc_hdr_num_code, a.invc_doc_hdr_num_code,
       a.invc_doc_ln_itm_num_code, a.key_sls_ordr_fact_id,
       a.sls_doc_hdr_num_code, a.sls_doc_ln_itm_num_code,
       a.sls_doc_schdl_ln_num_code, a.intrntnl_num_code, a.bill_qty_ind,
       a.shppng_pnt_code, a.invc_unit_qty, a.invc_net_amt,
       a.invc_whlsl_amt, a.invc_uom_of_cnvrsn_fctr,
       a.zzz_rcrd_chng_dttm, a.zzz_rcrd_create_dttm,
       a.zzz_rcrd_create_uid, a.orig_sls_doc_hdr_num_code,
       a.orig_sls_doc_ln_itm_num_code, a.ext_billing_hdr_doc
  FROM ops_stg.invc_fact a, OPS_SUPPORT.TMP_BW_2LIS13_BE_05182007 b
  where a.sls_doc_hdr_num_code = b.aubel
  and a.zzz_rcrd_chng_dttm between to_date('01-APR-2007', 'DD-MON-YYYY') AND to_date('10-APR-2007', 'DD-MON-YYYY')
  and a.zzz_rcrd_chng_dttm < b.zzz_rcrd_chng_dttm
  and b.vbeln NOT IN 
                     (select /*+ parallel (a2, 10) */
                     invc_doc_hdr_num_code from ops_stg.invc_fact a2)


TABLE A has this table structure: Primary Key KEY_INVC_FACT_CNCT_ID

SELECT a.key_invc_fact_cnct_id, a.key_invc_stat_dim_cnct_id,
       a.key_plnt_loctn_dim_cnct_id, a.key_itm_cat_dim_cnct_id,
       a.key_invc_doc_type_dim_cnct_id, a.key_invc_type_dim_cnct_id,
       a.key_crncy_dim_cnct_id, a.key_invc_date_dim_id,
       a.dlvry_doc_hdr_num_code, a.invc_doc_hdr_num_code,
       a.invc_doc_ln_itm_num_code, a.key_sls_ordr_fact_id,
       a.sls_doc_hdr_num_code, a.sls_doc_ln_itm_num_code,
       a.sls_doc_schdl_ln_num_code, a.intrntnl_num_code, a.bill_qty_ind,
       a.shppng_pnt_code, a.invc_unit_qty, a.invc_net_amt,
       a.invc_whlsl_amt, a.invc_uom_of_cnvrsn_fctr,
       a.zzz_rcrd_chng_dttm, a.zzz_rcrd_create_dttm,
       a.zzz_rcrd_create_uid, a.orig_sls_doc_hdr_num_code,
       a.orig_sls_doc_ln_itm_num_code, a.ext_billing_hdr_doc
  FROM ops_stg.invc_fact a


TABLE B PRIMARY KEY VBELN, POSNR

SELECT a.zzz_rcrd_chng_id, a.zzz_rcrd_insrt_id, a.zzz_rcrd_updt_id,
       a.zzz_rcrd_dlt_id, a.zzz_rcrd_re_insrt_id, a.zzz_src_archv_flag,
       a.zzz_src_extrct_dttm, a.zzz_src_rcrd_chksm_num, a.zzz_dml_code,
       a.zzz_sessn_run_id, a.zzz_rcrd_create_dttm, a.zzz_rcrd_chng_dttm,
       a.zzz_rcrd_create_uid, a.zzz_src_rcrd_chngd_clmn_btmp,
       a.zzz_rcrd_chng_uid, a.zzz_src_clnt_dim_id, a.zzz_partitn_id,
       a.request, a.datapakid, a.partno, a.record, a.rocancel, a.vbeln,
       a.posnr, a.aedat, a.bukrs, a.bzirk, a.fkart, a.fkdat, a.fktyp,
       a.hwaer, a.kdgrp, a.kunag, a.kunrg, a.kurrf, a.stwae, a.vbtyp,
       a.vkorg, a.vtweg, a.waerk, a.aktnr, a.aubel, a.aupos, a.bonba,
       a.bonus, a.brgew, a.brtwr, a.bwapplnm, a.bwvorg, a.charg,
       a.ean11, a.erdat, a.fareg, a.fbuda, a.fkimg, a.fklmg, a.gewei,
       a.knuma_ag, a.kokrs, a.kostl, a.kursk, a.kursk_dat, a.kvgr1,
       a.kvgr2, a.kvgr3, a.kvgr4, a.kvgr5, a.kzwi1, a.kzwi2, a.kzwi3,
       a.kzwi4, a.kzwi5, a.kzwi6, a.lgort, a.lmeng, a.matkl, a.matnr,
       a.matwa, a.meins, a.mvgr1, a.mvgr2, a.mvgr3, a.mvgr4, a.mvgr5,
       a.mwsbp, a.netwr, a.ntgew, a.pkunre, a.pkunwe, a.posar, a.prodh,
       a.provg, a.prsdt, a.pstyv, a.pvrtnr, a.skfbp, a.smeng, a.spara,
       a.spart, a.stadat, a.stcur, a.umvkn, a.umvkz, a.vdatu, a.vgbel,
       a.vgpos, a.vkbur, a.vkgrp, a.voleh, a.volum, a.vrkme, a.vstel,
       a.wavwr, a.werks, a.aufnr, a.augru_auft, a.j_3aetenr,
       a.j_3afkimg, a.j_3asize, a.j_3avgete, a.ps_posid, a.anzfkpos,
       a.periv, a.zz_auart, a.zz_ernam, a.zzdoc_shp_name1,
       a.zzdoc_shp_city1, a.zzdoc_shp_land1, a.zzdoc_shp_pstcd1,
       a.zzdoc_shp_regio, a.zzdoc_shp_street, a.zzdoc_shp_adr, a.belnr,
       a.fkart_ab, a.fkart_rl, a.fksto, a.kalsm, a.knuma, a.knumv,
       a.konda, a.mrnkz, a.pltyp, a.regio, a.rfbsk, a.zlsch, a.zterm,
       a.arktx, a.autyp, a.j_3aasdt, a."/AFS/FKMGK", a.j_3afklmg,
       a.j_3aknumh, a.ernam, a.j_4krcat, a.kdkg1, a.kdkg2, a.kdkg3,
       a.kdkg4, a.mprok, a.paobjnr, a.posnv, a.shkzg, a.vbelv, a.vgtyp,
       a.timestamp, a.zzvbeln_or, a.zzposnr_or
  FROM tmp_bw_2lis13_be_05182007 a
Re: Loading missing data from one table to another [message #238902 is a reply to message #238887] Sun, 20 May 2007 21:22 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Please post properly formatted EXPLAIN_PLAN for your final query.
Do all tables involved have current statistics?

[Updated on: Sun, 20 May 2007 21:42] by Moderator

Report message to a moderator

Re: Loading missing data from one table to another [message #238906 is a reply to message #238902] Sun, 20 May 2007 23:10 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
STATS are current.

Attached is EXPLAIN PLAN in EXCEL Format (File name: BW_VDITM_INC.xls)
Re: Loading missing data from one table to another [message #238976 is a reply to message #238906] Mon, 21 May 2007 01:43 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Attaching an Excel file wasn't that good idea ... you will not find many people here willing to download and open potentially dangerous files. You'd rather use a plain text format (or even post the explain plan directly in the message body).
Re: Loading missing data from one table to another [message #239115 is a reply to message #238899] Mon, 21 May 2007 09:10 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
concorde800 wrote on Sun, 20 May 2007 21:40
Syntax-wise its correct.


Sorry, no it's not:
FOO SCOTT>l
  1  SELECT a.col1, a.col2, a.col3
  2    FROM TABLE1 a, Table2 b
  3    where a.record_change_date between ('01-May-2007') and ('08-May-2007')
  4*     and where b.column1 NOT IN (Select a.column1 from Table B)
rsbgcdp1 BC>/
    and where b.column1 NOT IN (Select a.column1 from Table B)
        *
ERROR at line 4:
ORA-00936: missing expression


Additionally, fixing the syntax errors in a way to make it syntactically correct yet not necessarily the syntax you want as it made no sense at all, you still cannot compare DATE columns to strings.

FOO SCOTT>l
  1  SELECT a.col1, a.col2, a.col3
  2  FROM TABLE1 a, Table2 b
  3  where a.record_change_date between '01-May-2007' and '08-May-2007'
  4* and b.column1 NOT IN (Select column1 from Table2)
rsbgcdp1 BC>/
where a.record_change_date between '01-May-2007' and '08-May-2007'
                                   *
ERROR at line 3:
ORA-01858: a non-numeric character was found where a numeric was expected

Previous Topic: SELECT QUERY
Next Topic: Rename an table
Goto Forum:
  


Current Time: Thu Dec 08 20:11:07 CST 2016

Total time taken to generate the page: 0.16914 seconds