Date Filter Issue

990088

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*

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