Building a BO subquery in SQL

Anish Goswami

Content

Below mentioned are BO subqueries, need to use them in OBIEE. Can anyone list out anyway to do it..

 

Kiran Shenvi

Hi Anish,

Just wanted to check s there any specific reason you are using Application Tracking CSW Step/Status instead of Application Current CSW Step/Status?

Because in OBI Application Tracking CSW objects are now tracking only Submission Tracking events and not CSW events. 

For CSW Events we need to use Submission History objects. Using mix of both will complicate things.

Also, Req. Primary Location ID Level 1 is no present in OBI and you will have to use regular object Req. Primary Location Level 1.

Regards,

Kiran Shenvi

 

 

 

Kiran Shenvi

Hi Anish,

If you are looking for Current Step/Status you may try this and make changes accordingly >

(CASE WHEN "Requisition Primary Location"."Location Level1 Code" IN ('Value 1','Value 2') AND TIMESTAMPDIFF(SQL_TSI_DAY,"Submission CSW Status - Current"."Current Status Start Date", CURRENT_DATE) <=1 AND "Submission CSW Status - Current"."Current Step Name" = 'New (T)' AND "Submission CSW Status - Current"."Current Status Name" IN ('Rejected (T)','Move Forward (T)','Under Consideration (T)') THEN 1 ELSE 0 END) = 1

If History Step/Status >

MAX(CASE WHEN "Requisition Primary Location"."Location Level1 Code" IN ('Value 1','Value 2') AND TIMESTAMPDIFF(SQL_TSI_DAY,"Submission CSW Status - Historical"."Submission Historical Status Start Date", CURRENT_DATE) <=1 AND "Submission CSW Status - Historical"."Historical Step/Status Was Reverted" = 'No' AND "Submission CSW Status - Historical"."Historical Step Name" = 'New (T)' AND "Submission CSW Status - Historical"."Historical Status Name" IN ('Rejected (T)','Move Forward (T)','Under Consideration (T)') THEN 1 ELSE 0 END BY "Submission General Info"."Submission Identifier") = 1

Paste these in convert to SQL text box.

Hope this helps.

Regards,

Kiran Shenvi

 

 

Anish Goswami

Hi Kiran, 

Thanks for the response, we use application tracking CSW step and status, when we are looking for all such transaction that has happened to particular submission. So this helps us in having historical data to be used in reports. If there is requirement for any current CSW step/ status we use  Application Current CSW Step/Status. Yes, I agree that using transaction tracking is not as it does not take CSW events, instead if we use Historical Step/status. its not populating complete data. 

 

Anish Goswami

In this particular SubQuery is used for populating Most Recent Comment, date and user by. In OBI, we have used transaction tracking fields, as historical step/status was not populating data properly. Can you help me with particular sub query as well. 

Kiran Shenvi

Hi Anish,

Just one query to understand your BO requirement correctly.

1. Step/Status you are looking for History or Step/Status of last CSW activity i.e. means Current Step/Status.

2. Last Activity is tracked using "Is Most Recent" in OBI but do you want to additionally check if the latest activity within  24 hrs.

Regards,

Kiran Shenvi

 

Anish Goswami

Hi Kiran,

Response to query:

1. Step/Status you are looking for History or Step/Status of last CSW activity i.e. means Current Step/Status.

We are looking for History step/status.

2. Last Activity is tracked using "Is Most Recent" in OBI but do you want to additionally check if the latest activity within  24 hrs.

Most recent is " Is Most  Recent" 

Regards,

Kiran Shenvi

Anish Goswami

Hi Kiran,

Response to query:

1. Step/Status you are looking for History or Step/Status of last CSW activity i.e. means Current Step/Status.

We are looking for History step/status.

2. Last Activity is tracked using "Is Most Recent" in OBI but do you want to additionally check if the latest activity within  24 hrs.

Most recent is " Is Most  Recent" 

Regards,

Kiran Shenvi

Kiran Shenvi

Hi Anish,

I have found a probable solution.

Use both Transaction Tables and Historical CSW Tables and use below filters.

"Submission Tracking History"."Transaction is Most Recent" = Yes

&

RANK("Submission CSW Status - Historical"."Submission Historical Status Start Date" BY "Submission General Info"."Submission Identifier") = 1

This will bring the most recent in all the columns.

Later, the trick is check 2 date fields -> Latest Transaction Date and Latest Historical CSW Start Date, whichever is greater show the comments/user from respective sections.

I have attached a sample catalog file. You may unarchive it and test by hard-coding the sample submissions.

There are 3 tables :

1. Latest Transaction 

2. Latest History

3. Final columns

If this is successful then we can move with the final solution. 

Regards,

Kiran Shenvi