Skip to Main Content

Analytics Software

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Date Filter Issue

990088Mar 23 2013 — edited Mar 24 2013
I am facing issue with date filter when i am trying to apply date filter it been not carrying forward to the physical query. So its trying to pull all the records for Date from Database. Remaining all filters in calendar table and Organization table is working correctly. Guys, could you please provide me some suggestions on it?

Thanks

Presentation Query

SELECT
0 s_0,
"BCSS Biller"."Dim Calendar"."Fiscal Date" s_1
FROM "BCSS Biller"
WHERE
("Dim Calendar"."Fiscal Date" = date '2012-11-05')
ORDER BY 1, 2 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY

Physical Query

select distinct T152."FISCAL_DATE" as c1
from
*"NDW_REPORT_VIEWS"."DAY_DIM" T152*

Comments

MKKrishna
How are you passing the filter? through prompt?. Try checking by manually applying in the filter section.
Clear cache & Re-start all BI services & try again.
Check the Filter/Prompt columns are coming from proper physical table column mappings.
Check the DATATYPES of the columns.
GO to Physical Layer - Database - Properties - Features - Cross check the Date Literals.

Your query seems hitting database VIEWS work on the datatype of date.
990088
Hi MK,

I am manually applying filter. I have tried by clearing cache and restarting BI servers. Data type is DATE and column mappings are good. What literals i will be missing that i cant figure it out? Thanks

Edited by: 987085 on Mar 23, 2013 8:34 AM
SriniVEERAVALLI
Try this;
Pull date twice
for 1st date column filter with any data value, I assume you'll be using calendar control to select date
and for other column use this expression current_timestamp
and run it see how it behaves

If need you can send me email with screens

If helps mark
990088
Srini,

I Don't have any change. i am not sure which is missing here every other filter is working fine only date is missing.

SELECT
0 s_0,
"BCSS Biller"."Dim Calendar"."Fiscal Date" s_1,
"BCSS Biller"."Fact Seats"."Net Seats" s_2,
"BCSS Biller"."Fact Seats"."Seats Disconnects" s_3,
"BCSS Biller"."Fact Subs"."Net Subs" s_4,
"BCSS Biller"."Fact Subs"."Subs Disconnects" s_5
FROM "BCSS Biller"
WHERE
(("Dim Calendar"."Fiscal Date" IN (Current_timestamp)))
ORDER BY 1, 2 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY

select T152."FISCAL_DATE" as c3,
T136."Disconnects" as c5,
T136."Seats" as c6
from
"NDW_REPORT_VIEWS"."DAY_DIM" T152,
"BILLER"."CBCSS_SEATS_DM" T136
where ( T136."DAY_ID" = T152."DAY_ID" )

]]
[2013-03-23T12:27:32.000+00:00] [OracleBIServerComponent] [TRACE:5] [USER-18] [] [ecid: 64bfcb70599b1504:-e28e24f:13d977424c0:-8000-000000000000135a] [tid: 66c] [requestid: 70e60003] [sessionid: 70e60000] [username: hnaidu001c] -------------------- Sending query to database named CIS DEV (id: <<17189>>), connection pool named Connection Pool, logical request hash 3a2d64bf, physical request hash 95197090: [[
select T152."FISCAL_DATE" as c3,
T145."subs_disconnects" as c5,
T145."subs_connects" as c6
from
"NDW_REPORT_VIEWS"."DAY_DIM" T152,
"BILLER"."CBCSS_SUBS_DM" T145
where ( T145."DAY_ID" = T152."DAY_ID" )
SriniVEERAVALLI
hmmm are you sure about the query from NQquery.log ;)

I was asking you to just pull "BCSS Biller"."Dim Calendar"."Fiscal Date" twcie
and for 1st col set filter and other col with expression current_timestamp

No fact in the query.

once its showing right data just add fact column and see how it works.
990088
srini, when i am trying only fiscal date result set was showing no results with current_timestamp.
SriniVEERAVALLI
When you are not getting results I would suggest double check with my message to make sure you are following as I said.

I was asking you as below

SELECT "BCSS Biller"."Dim Calendar"."Fiscal Date" s_1, current_timestamp s_2 FROM "BCSS Biller"
WHERE "BCSS Biller"."Dim Calendar"."Fiscal Date" = timestamp '2013-03-21 00:00:00' ORDER BY s_1, s_2

hope now you got it Naidu ;)

thanks
990088
Srini, After executing that query i got result as Fiscal Date 3/21/2013 and Current time stamp 3/23/2013 9:28:58PM

SELECT
0 s_0,
"BCSS Biller"."Dim Calendar"."Fiscal Date" s_1,
current_timestamp s_2
FROM "BCSS Biller"
WHERE
("BCSS Biller"."Dim Calendar"."Fiscal Date" = timestamp '2013-03-21 00:00:00')
ORDER BY 1, 2 ASC NULLS LAST, 3 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY

Physical Query still is the same

select distinct T152."FISCAL_DATE" as c1
from
"NDW_REPORT_VIEWS"."DAY_DIM" T152
SriniVEERAVALLI
Something wrong! I need to see rpd. If possible can you send me? or the nqquery.log file?
990088
Srini, I have sent u a mail. Please look into it. Thanks
Turbokat
:) Naidu,

Try issuing direct DB request and see if how the results are coming up.

EXECUTE PHYSICAL CONNECTION POOL "Connection Pool" SELECT distinct FISCAL_DATE from NDW_REPORT_VIEWS.DAY_DIM WHERE FISCAL_DATE = timestamp '2013-03-21 00:00:00'

Let us know.

Thanks,
SVS
990088
Hey SVS its showing the result the date we requested. .but regular fiscal date still having problem.

This is the query log i am having.

EXECUTE PHYSICAL CONNECTION POOL "Connection Pool" SELECT distinct FISCAL_DATE from NDW_REPORT_VIEWS.DAY_DIM WHERE FISCAL_DATE = timestamp '2013-03-21 00:00:00'

SELECT distinct FISCAL_DATE from NDW_REPORT_VIEWS.DAY_DIM WHERE FISCAL_DATE = timestamp '2013-03-21 00:00:00'

Edited by: 987085 on Mar 24, 2013 8:30 PM
1 - 12
Locked Post
New comments cannot be posted to this locked post.