Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Unexpected results for a specific record in a table
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
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
Answers
-
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.
0 -
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.
0 -
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
0 -
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') )
0 -
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
0 -
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.
0 -
what is the data type of expiration date?. desc table?
what about first issue? is it resolved?
0 -
Hi Asim,
the column is of type DATE.
We are assuming that the actual issue behind the first issue would be this.
thanks
0 -
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).
0 -
Hi Asim,
0
