Cannot find specific date through Prompt

Supergluey · Apr 26, 2016 6:54AM

Hi All. Beginning User here for OBIEE.

I have problem right now where I am just playing with a table with dates (ex. 04/23/2016)

I have created a prompt where user selects a date and press ok and be presented with the data.

If i don't select and just press ok, it will display all the data.

In the dataset, let's say there is a date 02/20/2016, if I input the date through the prompt, it said there are no results.

However clearly there is because if I don't input the value, u can see the data!!!

The operator I used is / equal to, if I used other operators it will work.

This is a single header table and I been told I have to edit the SQL, but I'm kinda new to this and don't know where to put it or how to configured it.

Can anyone please help???

Thanks.

Pedro F · Apr 26, 2016 7:23AM

Hi,

It will be easier if you just put a couple of screenshot of what you're trying to do. Your description isn't very clear (at least, not for me).

Just put some images of your prompt and your report and that way it'll be easier for you to get an answer.

Supergluey · Apr 26, 2016 11:56PM

Okay sure,

The picture below is the result of the table I have selected.

I have configured a prompt for it, with the operator for "equal" as shown below.

So Using one of the dates from the first picture, I inputted into the prompt as shown below.

Supposedly, it should only show that data.

However, it gives me that no result can be found.

How is it possible? If it is shown when I don't input any date but not shown when I put in the date.

Therefore, I would want to know how I could configure it to show the dates I inputted in the result.

I have thought it was a format issue as it will put a 0 in front of the date while searching, but even after changing the format, it still resulted in the same problem.

As said, it is a single header table, so its not an issue between display value and code value.

Thanks

Sagar Tippe · Apr 27, 2016 2:55AM

Hi 3227388,

can you please select any value from this prompt and paste the SQL generated for the report? It will help us to identify what value this prompt is passing to the report?

Kind Regards,

Sagar Tippe

Supergluey · Apr 27, 2016 9:41PM

Dear Sagar,

Thanks for your reply.

The SQL below is if i don't input anything, and just press OK.

SELECT

0 s_0,

"Business Area : VALID IV EUC"."Ucdpmt"."Txn date" s_1

FROM "Business Area : VALID IV EUC"

ORDER BY 2 ASC NULLS LAST

FETCH FIRST 65001 ROWS ONLY

If I press a value, it would give me the no result message. I am not sure if you mean the SQL like that.

Please Help.

Sagar Tippe · May 4, 2016 9:59AM

I mean Physical SQL

Supergluey · May 5, 2016 2:51AM

Dear Sagar,

Thanks for your reply.

I don't know what you mean by Physical SQL.

I did a search and found this in the log.

SET VARIABLE SAW_SRC_PATH='/shared/COMMON/UCDPMT';SELECT

0 s_0,

"VALID V EUC"."Ucdpmt"."Height from dimension" s_1,

"VALID V EUC"."Ucdpmt"."Height to dimension" s_2,

"VALID V EUC"."Ucdpmt"."Left hand steering" s_3,

"VALID V EUC"."Ucdpmt"."Length from dimension" s_4,

"VALID V EUC"."Ucdpmt"."Length to dimension" s_5,

"VALID V EUC"."Ucdpmt"."Txn date" s_6,

"VALID V EUC"."Ucdpmt"."Veh id" s_7,

"VALID V EUC"."Ucdpmt"."Weight dimension" s_8,

"VALID V EUC"."Ucdpmt"."Width from dimension" s_9,

"VALID V EUC"."Ucdpmt"."Width to dimension" s_10

FROM "VALID V EUC"

ORDER BY 8 ASC NULLS LAST, 7 ASC NULLS LAST, 4 ASC NULLS LAST, 5 ASC NULLS LAST, 6 ASC NULLS LAST, 10 ASC NULLS LAST, 11 ASC NULLS LAST, 2 ASC NULLS LAST, 3 ASC NULLS LAST, 9 ASC NULLS LAST

FETCH FIRST 65001 ROWS ONLY

Do you mean like this?

If not , can you please tell me how to generate the Physical SQL?

Thanks

Sagar Tippe · May 9, 2016 1:18AM

This one is Logical SQL.

Physical SQL would be like this,

[2016-05-09T10:36:05.000+05:30] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: e53d89ac6aa93003:-647a51d6:15456ea2ae8:-8000-000000000006cdf2,0:1:1:5] [tid: 1151c] [requestid: 62820041] [sessionid: 62820000] [username: weblogic] -------------------- Sending query to database named Sample App Lite Data (id: <<31476>>), connection pool named SampleApp_Lite_Xml, logical request hash 98a79746, physical request hash ab1c5c14: [[

WITH

SAWITH0 AS (select sum(T126.Revenue) as c1,

T129.Office_Dsc as c2,

T131.Prod_Dsc as c3,

T131.Prod_Key as c4,

T129.Sequence as c5,

T131.Sequence as c6,

T129.Office_Key as c7

from

SAMP_OFFICES_D T129 /* D20 Offices */ ,

SAMP_PRODUCTS_D T131 /* D10 Product */ ,

SAMP_REVENUE_F T126 /* F10 Revenue Facts (Order grain) */

where ( T126.Prod_Key = T131.Prod_Key and T126.Office_Key = T129.Office_Key )

group by T129.Office_Dsc, T129.Office_Key, T129.Sequence, T131.Prod_Dsc, T131.Prod_Key, T131.Sequence)

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7, D1.c8 as c8 from ( select distinct 0 as c1,

D1.c2 as c2,

D1.c3 as c3,

D1.c4 as c4,

D1.c5 as c5,

D1.c6 as c6,

D1.c1 as c7,

D1.c7 as c8

from

SAWITH0 D1

order by c6, c5 ) D1 where rownum <= 65001

Supergluey · May 9, 2016 5:22AM

Dear Sagar,

I have managed to find it through one of the logs.

It is shown below.

[2016-05-09T17:17:11.355+08:00] [OBIS] [TRACE:2] [] [] [ecid: ] [sik: ssi] [tid: 1c18] [messageid: USER-0] [requestid: 515000c] [sessionid: 5150000] [username: admin] ############################################## [[

-------------------- SQL Request, logical request hash:

9be1427e

SET VARIABLE SAW_SRC_PATH='/shared/COMMON/UCDPMT',LOGLEVEL=2; SELECT

0 s_0,

"VALID V EUC"."Ucdpmt"."Height from dimension" s_1,

"VALID V EUC"."Ucdpmt"."Height to dimension" s_2,

"VALID V EUC"."Ucdpmt"."Left hand steering" s_3,

"VALID V EUC"."Ucdpmt"."Length from dimension" s_4,

"VALID V EUC"."Ucdpmt"."Length to dimension" s_5,

"VALID V EUC"."Ucdpmt"."Txn date" s_6,

"VALID V EUC"."Ucdpmt"."Veh id" s_7,

"VALID V EUC"."Ucdpmt"."Weight dimension" s_8,

"VALID V EUC"."Ucdpmt"."Width from dimension" s_9,

"VALID V EUC"."Ucdpmt"."Width to dimension" s_10

FROM "VALID V EUC"

ORDER BY 8 ASC NULLS LAST, 7 ASC NULLS LAST, 4 ASC NULLS LAST, 5 ASC NULLS LAST, 6 ASC NULLS LAST, 10 ASC NULLS LAST, 11 ASC NULLS LAST, 2 ASC NULLS LAST, 3 ASC NULLS LAST, 9 ASC NULLS LAST

FETCH FIRST 65001 ROWS ONLY

]]

[2016-05-09T17:17:11.373+08:00] [OBIS] [TRACE:2] [] [] [ecid: ] [sik: ssi] [tid: 1c18] [messageid: USER-23] [requestid: 515000c] [sessionid: 5150000] [username: admin] -------------------- General Query Info: [[

Repository: ssi, Subject Area: VALID V EUC, Presentation: VALID V EUC

]]

[2016-05-09T17:17:11.391+08:00] [OBIS] [TRACE:2] [] [] [ecid: ] [sik: ssi] [tid: 1c18] [messageid: USER-18] [requestid: 515000c] [sessionid: 5150000] [username: admin] -------------------- Sending query to database named VALID V EUC (id: <<644867>>), connection pool named ConnectionPool, logical request hash 9be1427e, physical request hash b12793f1: [[

WITH

SAWITH0 AS (select distinct T4.HEIGHT_FROM_DIMENSION as c1,

T4.HEIGHT_TO_DIMENSION as c2,

T4.LEFT_HAND_STEERING as c3,

T4.LENGTH_FROM_DIMENSION as c4,

T4.LENGTH_TO_DIMENSION as c5,

T4.TXN_DATE as c6,

T4.VEH_ID as c7,

T4.WEIGHT_DIMENSION as c8,

T4.WIDTH_FROM_DIMENSION as c9,

T4.WIDTH_TO_DIMENSION as c10

from

V4PRDEUOW.UCDPMT T4)

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7, D1.c8 as c8, D1.c9 as c9, D1.c10 as c10, D1.c11 as c11 from ( select 0 as c1,

D1.c1 as c2,

D1.c2 as c3,

D1.c3 as c4,

D1.c4 as c5,

D1.c5 as c6,

D1.c6 as c7,

D1.c7 as c8,

D1.c8 as c9,

D1.c9 as c10,

D1.c10 as c11

from

SAWITH0 D1

order by c8, c7, c4, c5, c6, c10, c11, c2, c3, c9 ) D1 where rownum <= 65001

]]

Please help.

Supergluey · May 12, 2016 5:26AM

Any Ideas?

Thomas Dodds · May 12, 2016 2:13PM

could be data types ... or the formatting of the date ... looks like your data has year as YY and prompt has YYYY ... if it is doing a text based comparison - then YY != YYYY ...

MFA for Oracle Community

Oracle Analytics Cloud and Server

Categories

Cannot find specific date through Prompt

Answers