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

How to create report on custom logical SQL in OBIEE 11g

3052987

Hi,

I want to create a report based on custom logical SQL. I came up with below logical SQL but when I execute this, OBIEE is throwing error: Invalid SQL identifier.

Logical SQL:

SELECT

a."p1" saw_0,

a."p2" saw_1,

a."p3" saw_2,

a."p4" saw_3,

a."p5" saw_4,

b."q2" saw_5,

b."q3" saw_6,

c."r2" saw_7,

c."r3" saw_8

FROM

(SELECT

"- Attribute"."Access Id" "p1",

SUM(CASE WHEN MONTH("- Create Time"."Day Date") = MONTH(DATE '2016-11-11') THEN 1 ELSE 0 END) "p2",

SUM(CASE WHEN MONTH("- Create Time"."Day Date") = (MONTH(DATE '2016-11-11')-1) THEN 1 ELSE 0 END) "p3",

SUM(CASE WHEN MONTH("- Create Time"."Day Date") = MONTH(DATE '2016-11-11') THEN 1 ELSE 0 END) + SUM(CASE WHEN MONTH("- Create Time"."Day Date") = (MONTH(DATE '2016-11-11')-1) THEN 1 ELSE 0 END) "p4",

SUM(CASE WHEN CAST("- Create Time"."Day Date" AS DATE) = DATE '2016-11-11' THEN 1 ELSE 0 END) "p5"

FROM "OAD"

WHERE SUM(CASE WHEN MONTH("- Create Time"."Day Date") = MONTH(DATE '2016-11-11') THEN 1 ELSE 0 END) + SUM(CASE WHEN MONTH("- Create Time"."Day Date") = (MONTH(DATE '2016-11-11')-1) THEN 1 ELSE 0 END) >= 5

AND "Client"."Client Name" = 'Ford Motor Company') a,

(SELECT

"- Attribute"."Access Id" "q1",

"- Attribute"."Create Time" "q2",

"- Transaction Type"."Transaction_type" "q3",

RANK(EVALUATE('F_GET_CREATE_TIMESTAMP(%1)' AS CHAR, "- Attribute"."Create Time") BY "- Attribute"."Access Id") "q4"

FROM "OAD"

WHERE RANK(EVALUATE('F_GET_CREATE_TIMESTAMP(%1)' AS CHAR, "- Attribute"."Create Time") BY "- Attribute"."Access Id") = 1

AND "Client"."Client Name" = 'Ford Motor Company') b,

(SELECT

"- Attribute"."Access Id" "r1",

"- Attribute"."Create Time" "r2",

"- Transaction Type"."Transaction_type" "r3",

RANK(EVALUATE('F_GET_CREATE_TIMESTAMP(%1)' AS CHAR, "- Attribute"."Create Time") BY "- Attribute"."Access Id") "r4"

FROM "OAD"

WHERE RANK(EVALUATE('F_GET_CREATE_TIMESTAMP(%1)' AS CHAR, "- Attribute"."Create Time") BY "- Attribute"."Access Id") = 2

AND "Client"."Client Name" = 'Ford Motor Company') c

WHERE a."p1" = b."q1"

AND a."p1" = c."r1"

Appreciate your help.

Thank You.

Find more posts tagged with

Accepted answers

All comments

rmoff

How are you trying to execute it?
What's your use case? Why can't you build an analysis using the RPD as normal?

3052987

Hi,

My table has data something like below -

CUST_ID DOMAIN CALLTIME REASON

C001 X 2017-01-02 12:30 PM A

C001 Y 2017-01-02 2:46 PM B

C001 X 2016-12-29 11:20 AM B

C002 Z 2017-01-10 1:20 PM C

C002 X 2016-12-28 11:10 AM B

C003 Y 2017-01-05 10:24 AM C

C003 Y 2016-12-30 2:40 PM D

C003 Z 2017-01-05 10:40 AM E

C003 X 2017-01-05 3:20 PM A

In My report, I would like to display something as below -

CUST_ID CallsPrevMonth# CallsCurMonth# Total PrevCallTime PrevCallReason RecentCallTime RecentCallReason

C001 1 2 3 2017-01-02 12:30 PM A 2017-01-02 2:46 PM B

C003 1 3 4 2017-01-05 3:20 PM A 2017-01-05 10:40 AM E

Since Total calls made by CUST_ID C002 is less than 3, hence it'll not be displayed in report.

I tried with RANK() function but was not able to pull previous call Time and Reason. Hence, opting to use 3 SQL blocks joining them and showing results.

Let me know if that is possible or is there any workaround?

Thank You.

rmoff

You didn't answer my question (1) above. To your answer to my second question, the 'workaround' would be to use OBIEE as it's generally intended and build your data model such that analyses can be built automatically through the Answers interface. If you're having to resort to hacking together logical SQL you're either misunderstanding how OBIEE should be used, or haven't built your RPD correctly.

3052987

Hi,

To answer your 1st question, I'm plugging this logical SQL in Analysis Simple SQL Statement box. The error then appeared.

I'm not sure how to fetch the previous call time from the data available. RANK() function didn't help in OBIEE.

Is there any way I can fetch previous call time?

Thanks...

Thomas Dodds

Build your fact table to be able to answer the question the business is asking ... so you have your base table ... just add a routine on your post-load-processing to pivot the data (databases handle data better than the logical smarts of OBIEE) then model this fact into OBIEE.

Even a materialized view in your database will perform much better and the SQL to get what you want is pretty strait forward ...

3052987

Hi Thomas,

Can you please explain how pivoting the data would help me? The user wants to have interactive dashboard with Domain and Reason acting as dashboard prompts.

So, business would like to see data for a particular domain and/or reason and/or no filters applied.

Thanks,

Rituraj

Thomas Dodds

You can still have domain and reason as prompted values ... the value in the physical structure is PERFORMANCE, reuse and ease of maintenance.

So you end up with two physical structures that can satisfy the requirements ... the original one gives the complete history, the new one with the most recent items on the same row as the 'current' item ... you are still going to use the dimensions etc

3052987

Can you please demonstrate how my new physical table would look like that can answer these questions?

Thanks,

Rituraj

Thomas Dodds

You have already showed it:

CUST_ID CallsPrevMonth# CallsCurMonth# Total PrevCallTime PrevCallReason RecentCallTime RecentCallReason

C001 1 2 3 2017-01-02 12:30 PM A 2017-01-02 2:46 PM B

C003 1 3 4 2017-01-05 3:20 PM A 2017-01-05 10:40 AM E

Set this up as a snapshot of the baas table at a regular interval (seems monthly; or weekly with rolling month snapshot)

3052987

Hi Thomas,

If my new table contains only the records that I need to show on report, then how will the interaction with Domain dashboard prompt would work. Say, If i select Domain 'X', the there would be no output. I'm not sure with new table structure, as you've mentioned above, how report will work.

Thanks,

Rituraj