This discussion is archived
1 Reply Latest reply: Jan 10, 2013 11:44 PM by Karthick_Arp RSS

Compare two views data and insert the missing rows

751828 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points