Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Weird results from query
Hello all,
I need an advise with the following query. While executing the query it displays the following results:
QUERY
select /*+ parallel(t1,8) */
NVL(sum(decode(bitand(ethnicity_id1,1),0,0,1)),0) as "ethnicity|Asian or Pacific Isl",
NVL(sum(decode(bitand(ethnicity_id1,4),0,0,1)),0) as "ethnicity|Hispanic/Latino",
NVL(sum(decode(bitand(ethnicity_id1,8),0,0,1)),0) as "ethnicity|Native American or A",
NVL(sum(decode(bitand(ethnicity_id1,16),0,0,1)),0) as "ethnicity|White/Caucasian",
NVL(sum(decode(bitand(ethnicity_id1,32),0,0,1)),0) as "ethnicity|Other Race",
NVL(sum(decode(bitand(ethnicity_id1,64),0,0,1)),0) as "ethnicity|Decline To Answer",
NVL(sum(decode(bitand(ethnicity_id1,2),0,0,1)),0) as
"ethnicity|African-American",
NVL(sum(decode(bitand(ethnicity_id1,128),0,0,1)),0) as "ethnicity|Asian-Indian",
NVL(sum(decode(bitand(ethnicity_id1,256),0,0,1)),0) as "ethnicity|South Asian",
NVL(sum(decode(bitand(ethnicity_id1,512),0,0,1)),0) as "ethnicity|Chinese",
NVL(sum(decode(bitand(ethnicity_id1,1024),0,0,1)),0) as "ethnicity|Korean",
NVL(sum(decode(bitand(ethnicity_id1,2048),0,0,1)),0) as "ethnicity|Japanese",
NVL(sum(decode(bitand(ethnicity_id1,4096),0,0,1)),0) as "ethnicity|Other Southeast Asia",
NVL(sum(decode(bitand(ethnicity_id1,8192),0,0,1)),0) as "ethnicity|Filipino",
NVL(sum(decode(bitand(ethnicity_id1,16384),0,0,1)),0) as "ethnicity|Arab/West Asian",
NVL(sum(decode(bitand(ethnicity_id1,32768),0,0,1)),0) as "ethnicity|First Nation/Native ",
NVL(sum(decode(bitand(ethnicity_id1,65536),0,0,1)),0) as "ethnicity|Mixed Racial Backgro"
from collapsed_by_email t1;
RESULTS
This returns almost same
numbers by demo item, which is not correct.
ethnicity|Asian or Pacific Isl 430653
ethnicity|Hispanic/Latino 430630
ethnicity|Native American or A 430621
ethnicity|White/Caucasian 430672
ethnicity|Other Race 430649
ethnicity|Decline To Answer 430624
ethnicity|African-American 433301
ethnicity|Asian-Indian 430622
ethnicity|South Asian 430620
ethnicity|Chinese 430621
ethnicity|Korean 430621
ethnicity|Japanese 430621
ethnicity|Other Southeast Asia 430620
ethnicity|Filipino 430620
ethnicity|Arab/West Asian 430620
ethnicity|First Nation/Native 430620
ethnicity|Mixed Racial Backgro 430621
But, when I execute this
select
i.value, i.description, d.total_emails, trunc(d.total_emails/r.subscriber_count*100,2) percent,
r.refresh_date, r.item, r.subscriber_count, r.subscriber_count_compound,
d.total_unique_emails, d.total_emails, i.description, trunc(d.total_emails/r.subscriber_count*100,2)
percent
--sum( total_emails ), sum( total_unique_emails ) --
from dc_bync_runs r, dc_bync_stats d, item_values i,
most_recent_dc_bync_id m
where r.dc_bync_id=m.dc_bync_id and r.dc_bync_id=d.dc_bync_id and r.item=i.item and d.value=i.value
and m.item ='ethnicity'
and r.refresh_date > sysdate - 60;
I get this result
DESCRIPTION TOTAL_EMAILS
Asian or Pacific Islander 19290
Hispanic/Latino 23006
Native American or Alaskan Native 5085
White/Caucasian 251115
Other Race 83374
Decline To Answer 10459
African-American 40389
Asian-Indian 15571
South Asian 3661
Chinese 6418
Korean 567
Japanese 1161
Other Southeast Asian 3075
Filipino 4565
Arab/West Asian 3644
First Nation/Native Canadian 509
Mixed Racial Background 5273
I need an advise from you guys, I've checked the codes so far and they are supposed to return the same values, but the first one is returning wrong data for some reason. Do you have any advise?
Découvrez les photos les plus intéressantes du jour!
Découvrez les styles qui font sensation sur Yahoo! Québec Avatars.
http://cf.avatars.yahoo.com/
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 12 2007 - 11:12:00 CST