1 Reply Latest reply: Jan 11, 2013 1:44 AM by Karthick_Arp RSS

    Compare two views data and insert the missing rows

    751828
      V_EXPENSE_TYPE :(view)
      ==================
      CREATE OR REPLACE FORCE VIEW "SPIDERINT"."V_EXPENSE_TYPE" ("EXPENSE_TYPE_ID", "EXPENSE_CODE", "TEXT",
      "CUSTOM_EXPENSE", "LANGUAGE_CODE", "COUNTRY_ID", "EXPENSE_CATEGORY") AS
      SELECT t.itemid EXPENSE_TYPE_ID, et.EXPENSE_CODE, t.description TEXT, et.CUSTOM_EXPENSE,
      t.LANGUAGE_CODE, et.COUNTRY_ID, et.EXPENSE_CATEGORY
      FROM
      t_text t, T_EXPENSE_TYPE et
      WHERE
      T.GROUPCODE = 'EXPENSETYPE'
      and et.EXPENSE_TYPE_ID = t.ITEMID
      UNION
      SELECT EXPENSE_TYPE_ID, EXPENSE_CODE, TEXT, CUSTOM_EXPENSE, 'EN' ,COUNTRY_ID, EXPENSE_CATEGORY
      FROM T_EXPENSE_TYPE;


      Table T_EXPENSE_TYPE :
      -----------------------
      CREATE TABLE "SPIDERINT"."T_EXPENSE_TYPE"
      (     "EXPENSE_TYPE_ID" NUMBER(8,0),
           "EXPENSE_CODE" VARCHAR2(20 BYTE),
           "TEXT" VARCHAR2(60 BYTE),
           "CUSTOM_EXPENSE" CHAR(1 BYTE),
           "CHANGED_BY" VARCHAR2(8 BYTE),
           "CHANGED_DATE" DATE,
           "ENTERED_BY" VARCHAR2(12 BYTE),
           "ENTERED_DATE" DATE,
           "COUNTRY_ID" NUMBER(8,0),
           "EXPENSE_CATEGORY" CHAR(1 BYTE),
           "T_MODE" CHAR(2 BYTE),
           "T_TIME" DATE,
           "EXPENSE_DESC" VARCHAR2(200 BYTE),
           CONSTRAINT "PK_T_EXPENSE_TYPE" PRIMARY KEY ("EXPENSE_TYPE_ID") ENABLE
      ) ;

      Table T_TEXT :
      -----------------------
      CREATE TABLE "SPIDERINT"."T_TEXT"
      (     "GROUPCODE" VARCHAR2(12 BYTE),
           "ITEMID" NUMBER(7,0),
           "LANGUAGE_CODE" VARCHAR2(3 BYTE),
           "DESCRIPTION" VARCHAR2(320 BYTE),
           "CHANGED_BY" VARCHAR2(12 BYTE),
           "CHANGED_DATE" DATE,
           "ENTERED_BY" VARCHAR2(12 BYTE),
           "ENTERED_DATE" DATE,
           "MAINTAIN" CHAR(1 BYTE) DEFAULT 'N',
           CONSTRAINT "PK_T_TEXT" PRIMARY KEY ("GROUPCODE", "ITEMID", "LANGUAGE_CODE") ENABLE,
           CONSTRAINT "FK_T_TEXT_LANGUAGE__T_LANGU" FOREIGN KEY ("LANGUAGE_CODE")
           REFERENCES "SPIDERINT"."T_LANGUAGE" ("LANGUAGE_CODE") ENABLE
      ) ;

      For Example :
      ---------------

      select * from T_Language --- 10 languages (EN,VK,DE,DK,MY,NL,NO,PO,ES,US)


      select * from V_EXPENSE_TYPE
      where CUSTOM_EXPENSE = 'N'
      AND LANGUAGE_CODE = 'EN'
      AND COUNTRY_ID is null ---- total 81 records

      EXPENSE_TYPE_ID     EXPENSE_CODE     TEXT     CUSTOM_EXPENSE     LANGUAGE_CODE     COUNTRY_ID     EXPENSE_CATEGORY
      1     EXA     Administration fee     N     EN     (null)     (null)
      2     EXL     Lunch     N     EN     (null)     (null)
      5     EXS     Other course related costs     N     EN     (null)     (null)
      6     REEXAM     Re-examination costs     N     EN     (null)     (null)
      7     OPITO     Opito charges     N     EN     (null)     (null)
      8     OLF     OLF charges     N     EN     (null)     (null)
      9     PSL     Personal safety log     N     EN     (null)     (null)
      13     ALLOW     Allowance fee     N     EN     (null)     (null)
      14     MAT     Extra charges/material     N     EN     (null)     (null)
      15     DINNER     Dinner     N     EN     (null)     (null)
      16     NIGHTMEA     Nightmeal     N     EN     (null)     (null)
      17     LOC     Location costs     N     EN     (null)     (null)
      18     BREAKFAST     Breakfast     N     EN     (null)     (null)
      50     TC-ATCFEE     ADMIN: ATC FEE     N     EN     (null)     (null)
      and so on
      select EXPENSE_TYPE_ID,EXPENSE_CODE from V_EXPENSE_TYPE
      where CUSTOM_EXPENSE = 'N'
      AND LANGUAGE_CODE = 'DK'
      AND COUNTRY_ID is null --- 17 records
      EXPENSE_TYPE_ID     EXPENSE_CODE     TEXT     CUSTOM_EXPENSE     LANGUAGE_CODE     COUNTRY_ID     EXPENSE_CATEGORY
      1     EXA     Adm. Gebyr     N     DK     (null)     (null)
      2     EXL     Dagsforplejning      N     DK     (null)     (null)
      5     EXS     Andet     N     DK     (null)     (null)
      6     REEXAM     re-eksamens pris     N     DK     (null)     (null)
      7     OPITO     Opito priser     N     DK     (null)     (null)
      8     OLF     OLF priser     N     DK     (null)     (null)
      9     PSL     Personlig sikkerheds log     N     DK     (null)     (null)
      13     ALLOW     tillægs gebyr     N     DK     (null)     (null)
      14     MAT     mer pris/materiale     N     DK     (null)     (null)
      15     DINNER     Aftensmad     N     DK     (null)     (null)
      16     NIGHTMEA     Nattmad     N     DK     (null)     (null)
      17     LOC     Location costs     N     DK     (null)     (null)
      Total "EN" has 81 records and "DK" have 17.
      Now i need to compare this DK with EN based on EXPENSE_TYPE_ID and EXPENSE_CODE, if exists then no need to insert new row.
      IF not exists then it should insert new row that are exists in EN with the same data but with language_code change as DK.So that DK also will
      be having 81 rows

      Can anyone help me how to attain this,

      Edited by: user9093700 on Jan 11, 2013 1:52 PM
        • 1. Re: Compare two views data and insert the missing rows
          Karthick_Arp
          I am not sure if i understand your requirement properly, but would it not work if you just modify your view like this
          create or replace force view spiderint.v_expense_type 
          (
             expense_type_id, 
             expense_code, 
             text, 
             custom_expense, 
             language_code, 
             country_id,
             expense_category
          ) 
          as 
          select t.itemid expense_type_id, 
                 et.expense_code, 
                 t.description text, 
                 et.custom_expense, 
                 t.language_code, 
                 et.country_id,
                 et.expense_category 
            from t_text t, 
                 t_expense_type et 
           where t.groupcode = 'EXPENSETYPE'
             and et.expense_type_id = t.itemid
          union 
          select expense_type_id, 
                 expense_code,
                 text, 
                 custom_expense, 
                 'EN' ,
                 country_id, 
                 expense_category
            from t_expense_type
           union  
          select expense_type_id, 
                 expense_code,
                 text, 
                 custom_expense, 
                 'DK' ,
                 country_id, 
                 expense_category
            from t_expense_type;