0 Replies Latest reply: Mar 13, 2012 10:30 AM by 923548 RSS

    Query Automation Data Warehouse--Validating data

      NEED Help..
      Below script is for comparing data between Three tables: Source Table(EXT_GES_EVENTS) to STG_GES_Character_create and STG_GES_EVENT_HISTORY..
      I want to compare the each field using Hook_id and Time_stamp, right now I have connected each columns of a table to other columns of tables, the columns order are not in the same order in the other tables…This is in Data warehouse, so the data from EXT_GES_EVENTS will be transformed to other tables in the different names, just the column names, value will be the same.
      I need to know if there is way to compare the data between the tables using everything I as did except joining from and E.Data1=C.Account_id . When I do this for 100 tables, it would be difficult to connect each field to field and will end up having a lot of queries..

      All I need to know is how I compare three tables: One source table to two tables;check value in the source table is exists in the other two table by Hook_id and TimeStamp.
      When all value match, count the record for each hook.

      Your help is appreciated:

      set serveroutput on

      intTotalCount number;

      FOR i in (Select distinct hook_id From ext_ges_events where hook_id not in (113,141,365))
      -- Hook 1 count STGCharacterCreate
      --dbms_output.put_line('Hook_id    :'||to_char(i.hook_id) );

      IF i.hook_id = 1 THEN
      select count(*) into intTotalCount
      FROM (select E.*
      from ext_ges_events E, STG_GES_CHARACTER_CREATE C, STG_GES_Event_history B,shard_dim sd
      where E.hook_id=i.hook_id
      --and C.Hook_id=i.hook_id
      and B.Hook_id=i.hook_id
      and E.shard_name = sd.display_name and unixts_to_date(E.event_timestamp) = C.event_date
      and unixts_to_date(E.event_timestamp) = B.event_date
      and sd.shard_dim_id =C.shard_dim_id
      and sd.shard_dim_id=B.Shard_dim_id
      and E.Data1=C.Account_id
      and E.Data1=B.Account_id
      and E.Data2=C.Character_id
      and E.Data2=B.Character_id
      and E.Data3=C.Character_name
      and E.Data3=B.STR_data_field_1
      and E.Data4=C.IP_Address
      and E.Data4=B.STR_data_field_2
      and E.Data5=C.Character_class_id
      and E.Data5=B.Num_data_field_1
      and E.Data6=C.Character_race_id
      and E.Data6=B.Num_data_field_2
      and E.Data7=C.Gender_code
      and E.Data7=B.Num_data_field_3
      and E.Data8=C.Body_id
      and E.Data8=B.Num_data_field_4
      and E.Data9=C.age_id
      and E.Data9=B.Num_data_field_5
      and E.Data10=C.Boot_id
      and E.Data10=B.Num_data_field_6
      and E.Data11=C.bracer_id
      and E.Data11=B.Num_data_field_7
      and E.Data12=C.chest_id
      and E.Data12=B.Num_data_field_8
      and E.Data13=C.complexion_id
      and E.Data13=B.Num_data_field_9
      and E.Data14=C.creature_id
      and E.Data14=B.Num_data_field_10
      and E.Data15=C.eyecolor_id
      and E.Data15=B.Num_data_field_11
      and E.Data16=C.face_id
      and E.Data16=B.Num_data_field_12
      and E.Data17=C.facehair_id
      and E.Data17=B.Num_data_field_13
      and E.Data18=C.Facepaint_id
      and E.Data18=B.Num_data_field_14
      and E.Data19=C.hair_id
      and E.Data19=B.Num_data_field_15
      and E.Data20=C.haircolor_id
      and E.Data20=B.Num_data_field_16
      and E.Data21=C.Hand_id
      and E.Data21=B.Num_data_Field_17
      and E.Data22=C.Head_id
      and E.Data22=B.Num_data_field_18
      and E.Data23=C.leg_id
      and E.Data23=B.Num_data_field_19
      and E.Data24=C.skincolor_id
      and E.Data24=B.Num_data_field_20
      and E.Data25=C.waist_id
      and E.Data25=B.Num_data_field_21
      and E.Data26=C.garmenthue_id
      and E.Data26=B.Num_data_field_22
      and E.Data27=C.Colorscheme_id
      and E.Data27=B.Num_data_field_23);
      dbms_output.put_line('Hook_id :'||to_char(i.hook_id) );
      dbms_output.put_line('RecCount :'|| intTotalCount);
      END IF;

      -- for the hook 4
      IF i.hook_id= 4 THEN
      select count (*) into intTotalCount
      from (Select E.*
      FROM ext_ges_events E, STG_GES_CHARACTER_XP C, shard_dim sd,Stg_ges_event_history B
      where E.hook_id =i.hook_id and E.shard_name = sd.display_name
      and unixts_to_date(E.event_timestamp) = C.event_date and sd.shard_dim_id = C.shard_dim_id
      and unixts_to_date(E.event_timestamp) = B.event_date and sd.shard_dim_id = B.shard_dim_id and B.Hook_id=i.hook_id
      and E.Hook_id=i.hook_id
      and E.Hook_id=i.hook_id and C.Hook_id=i.hook_id
      and E.data1=C.Account_id
      and E.data1=B.Account_id
      and E.Data2=C.Character_id
      and E.Data2=B.Character_id
      and E.Data3=C.source_id
      and E.Data3=B.Num_data_field_1
      and E.Data4=C.xp_amount
      and E.Data4=B.Num_data_field_2
      and E.Data5=C.Source_type
      and E.Data5=B.Num_data_field_3
      and E.Data6=C.character_level
      and E.Data6=B.Num_data_field_4
      and E.Data7=C.gsp
      and E.Data7=B.Num_data_field_5
      and E.Data8=C.grouped_flag
      and E.Data8=B.Num_data_field_6);
      dbms_output.put_line('Hook_id :'||to_char(i.hook_id) );
      dbms_output.put_line('RecCount :'|| intTotalCount);
      --dbms_output.put_line('RecCount      :'|| intTotalCount);
      end if;
      end loop;

      Sample Result for the above script:
      anonymous block completed
      Hook_id :1
      RecCount :1
      Hook_id :4
      RecCount :1



      Edited by: 920545 on Mar 13, 2012 8:30 AM