0

This view selects 696 entries. The CSV file has 48 entries.

CREATE OR REPLACE VIEW insert_3_char_abts AS SELECT
    ext.construct_id,
    n_term,
    enz_name,
    c_term,
    cpp,
    mutations,
    ext.g_batch,
    ext.p_batch,
    emptycol,
    
    c_batch,
    abts5_mean,
    abts5_SD,
    abts5_n,
    abts5_method,
    abts5_study_id,
    abts7_mean,
    abts7_SD,
    abts7_n,
    abts7_method,
    abts7_study_id,
    pur.pk_purified_enz_id
FROM EXTERNAL (( 
      construct_id NUMBER(10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      g_batch VARCHAR2 (50),
      p_batch VARCHAR2 (50),
      emptycol VARCHAR2(50),

      c_batch VARCHAR2 (50),
      abts5_mean NUMBER (5, 2),
      abts5_SD NUMBER (5, 2),
      abts5_n NUMBER (3),
      abts5_method VARCHAR2 (50),
      abts5_study_id VARCHAR2 (8),
      abts7_mean NUMBER (5, 2),
      abts7_SD NUMBER (5, 2),
      abts7_n NUMBER (3),
      abts7_method VARCHAR2 (50),
      abts7_study_id VARCHAR2 (8))
      
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE 
        SKIP 1
        BADFILE bad_files:'badflie_view_before_insert_char_abts.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL 
        ) 
    LOCATION ('CHAR_ABTS.CSV')
    REJECT LIMIT UNLIMITED) ext 
INNER JOIN purified_enz pur
ON ext.p_batch = pur.p_batch
INNER JOIN produced pr 
ON pr.pk_produced_id = pur.fk_produced_id;
;

If I finish this statement with

AND pr.fk_construct_id = ext.construct_id;

It selects 46 out of 48 records, which is better, but not great.

4
  • 1
    Assuming your CSV really has only 48 records, because your joins introduce duplicate rows. – Balazs Papp Aug 23 '20 at 23:09
  • @BalazsPapp pk_produced_id is a pk column. if it's all inner joins, i thought joining on a pk column would get rid of duplicates. – ellie-lumen Aug 23 '20 at 23:12
  • You will need to explain what that data represents and how the three tables (EXT, PUR and PR) relate to each other in an entity-relationship diagram. Then verify that the data you have is conformant to what the ER diagram says. It may be that it is not. In particular, do you have proper declarative referential integrity rules (= a FK constraint) between PR and PUR ? Enforcing referential integrity between EXT and the two others is not possible because EXT is an external table, so all you can do is validate the data. – Albert Godfrind Aug 24 '20 at 7:22
  • @ellie-lumen And what about pur.p_batch? Is pk_produced_id the only column in the PK? Is the PK enabled and validated? Even though pk_produced_id is really a single column, enabled and validated PK, you may already have data in your tables with the same p_batch value(s) what you have in your CSV. We do not know your data and relation between the tables. – Balazs Papp Aug 24 '20 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.