Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Weird results from query

Weird results from query

From: Isabel Bedoya <>
Date: Wed, 12 Dec 2007 09:12:00 -0800 (PST)
Message-ID: <>

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.

Received on Wed Dec 12 2007 - 11:12:00 CST

Original text of this message