Currently Being Moderated
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
DECLARE
intTotalCount number;
Begin
FOR i in (Select distinct hook_id From ext_ges_events where hook_id not in (113,141,365))
loop
-- 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;
End;
Sample Result for the above script:
anonymous block completed
Hook_id :1
RecCount :1
Hook_id :4
RecCount :1
--------------------------------
Missluxi@yahoo.com
Edited by: 920545 on Mar 13, 2012 8:30 AM
