Skip to Main Content

Data Science & Machine Learning

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query Automation Data Warehouse--Validating data

923548Mar 13 2012 — edited Mar 13 2012
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

Comments

User_TMTV7

The issue is reproducible if I were to attempt an auto complete in a following statement were the statement before it has a syntax error. The completion fails, the CPU usage shoots up as a result.
It'd be great if someone can reproduce this problem. Alternatively, is there somewhere I can raise a bug report?

thatJeffSmith-Oracle

My Oracle Support

User_TMTV7

Per this discussion (https://community.oracle.com/tech/developers/discussion/4051424/where-can-i-report-an-sqldeveloper-bug) I gather that's for paying customers. I, however, am not one. So I guess leave this post here and check back later.

thatJeffSmith-Oracle

correct, your company/org/customer needs a valid support contract for the database
i can take a look at your use case, but does the CPU issue happen even if i'm not using templates? or is it only when templates are involved? and if so, how many templates do you have?

User_TMTV7

FWIW, my employer does have an Oracle license - our production DB is Oracle. But I'm posting this on personal capacity; and I doubt the employer will be happy to associate my personal account to the official official one.
Since your last message, I tried to reproduce the issue on a fresh worksheet, but I wasn't able to get a completion to fail. I suspect sure the failed completion (when that happens) is only a trigger, and that the underlying reason ought have happened much before the completion fail, and that the completion failure only pushes it over the edge.
To answer your question: I think the CPU usage issue happens with any failed completion, not just template. I originally mentioned templates because I tend to use them often, but IIRC, I've encountered the issue even for other failed completions. Also, I have only a dozen or so templates configured, not more.
Is there any diagnosis I can pull out when I hit the issue next? I don't suppose 'top' showing 100% usage will do much help.

Java stack trace would pinpoint to the culprit.

User_TMTV7

Thanks; will grab one the next time I hit the issue.

User_TMTV7

It took a while for me to hit the issue again, but I'm attaching the stack dump of the process hitting high CPU usage.
sqldeveloper_jstack.txt (29.76 KB)

1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 10 2012
Added on Mar 13 2012
0 comments
190 views