nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Unexpected results for a specific record in a table

3427576

Hi,

1) I want to generate a simple report which should list the no of points expire for a specific time period by member. The results are fine for all except for a specific record. Two entries are being retrieved for the member TJ-1415. I am unable to find out the root cause for this. So need your inputs...

The criteria tab for the above report is

1.1.JPG

2) while digging into this I came across the following which are abnormal too

The respective criteria tab is

3) more strange results

I though I would get one entry for expiry that is 24 which is not the case

Find more posts tagged with

Accepted answers

All comments

asim cholas

Check the physical query and see if you have the same data in database. Ideally it will be, Also check if your expiration date has a timestamp in DB.

Gianni Ceresa

Your "Expiry" column isn't aggregated, so 2 rows in a source table = 2 rows in the final report.

Check your data as the issue is there. If you look into logs you find the exact physical query executed which will tell you if you maybe have a missing condition on one of your joins or tables.

3427576

Thanks for you replies.

Yes I do checked at DB level, as you guys suggested there are two records over there. My doubt is why two records are being pulled for only one member. According to the source OLTP DB this particular member the right value should be only 24. Please advise

3427576

The physical query generated from OBIEE is as follows

select distinct T460193.Expiry as c1,

T155106.X_MEMBER_NUM as c2,

T155106.X_PROGRAM_NAME as c3

from

W_LOY_MEMBER_D T155106 / Dim_W_LOY_MEMBER_D / ,

(select

a.member_wid as ROW_WID,

a.expiration_dt as ExpirationDate,

sum(a.accrualed_value) as AccruedValue,

sum(a.x_used_value) as UsedValue,

sum(a.accrualed_value - a.x_used_value) as Expiry,

p.point_type_name as PointType,

extract(month from a.expiration_dt) as ExpirationMonth,

extract(year from a.expiration_dt) as ExpirationYear,

to_char(a.expiration_dt,'MM-YYYY') as ExpirationMMYY

from w_loy_accrual_item_f a,

w_loy_point_type_d p

where

a.pointtype_wid = p.row_wid

group by

a.member_wid,

p.point_type_name,

a.expiration_dt) T460193

where ( T155106.ROW_WID = T460193.ROW_WID and T155106.X_MEMBER_NUM = 'TJ-1415'

and T155106.X_PROGRAM_NAME = 'TAPPMe' and T460193.PointType = 'TAPPMe' and

T460193.ExpirationDate between TO_DATE('2017-12-02' , 'YYYY-MM-DD') and TO_DATE('2018-01-01' , 'YYYY-MM-DD') )

asim cholas

what is the result it fetches for

select row_wid,

X_MEMBER_NUM,

T155106.X_PROGRAM_NAME

from W_LOY_MEMBER_D

where X_MEMBER_NUM = 'TJ-1415'

and X_PROGRAM_NAME = 'TAPPMe'

and for the below

select

a.member_wid as ROW_WID,

a.expiration_dt as ExpirationDate,

sum(a.accrualed_value) as AccruedValue,

sum(a.x_used_value) as UsedValue,

sum(a.accrualed_value - a.x_used_value) as Expiry,

p.point_type_name as PointType,

extract(month from a.expiration_dt) as ExpirationMonth,

extract(year from a.expiration_dt) as ExpirationYear,

to_char(a.expiration_dt,'MM-YYYY') as ExpirationMMYY

from w_loy_accrual_item_f a,

w_loy_point_type_d p

where

a.pointtype_wid = p.row_wid and

PointType = 'TAPPMe' and

T460193.ExpirationDate between TO_DATE('2017-12-02' , 'YYYY-MM-DD') and TO_DATE('2018-01-01' , 'YYYY-MM-DD')

and a.member_wid in(The result set from first Query)

group by

a.member_wid,

p.point_type_name,

a.expiration_dt

3427576

Hi,

At the db level the data is coming up correctly when the date filter is ExpirationDate between '30-dec-17' and '01-Jan-18'

don't working when the filter is ExpirationDate between '30-dec-17' and '31-dec-17'

please advise.

asim cholas

what is the data type of expiration date?. desc table?

what about first issue? is it resolved?

3427576

Hi Asim,

the column is of type DATE.

We are assuming that the actual issue behind the first issue would be this.

thanks

asim cholas

what is the result set for above two queries i posted? Also check the query which is generated when you have no records(till 31st Dec 2017).

3427576

Hi Asim,