Convert WEBI report FORALL function into Equivalent SQL Logic

[#1]

Hi,

I am trying to convert webi formulas into equivalent Oracle SQL code. But I am confused when using some functions (FOREACH and FORALL). Can anyone please look at the below formula and help me converting it into equivalent Oracle SQL code.

FYI, I am using BO 4.1 SP 9.

WEBI FORMULA 2:

[Baseline text] = If([Baseline Identifier]=0) Then "Current Cost Baseline" Else If ([Baseline Identifier] =Max([Baseline Identifier]) ForAll([Baseline Identifier];[Approval Date];[Finish FY Number];[FY1 Estimated Amount];[Total Estimated Amount] )) Then "Original Baseline" Else "Re-baseline"

(Universe Object SQL Definitions:

[[Baseline Identifier]= DW.CAPTL_INVMT_BSLINE.BASELINE_ID

[Approval Date]= DW.CAPTL_INVMT_BSLINE.APRVL_DT

[Finish FY Number]= DW.CAPTL_INVMT_BSLINE.FINISH_FY_NO

[FY1 Estimated Amount]= DW.CAPTL_INVMT_BSLINE.FY1_EST_AM

[Total Estimated Amount]= DW.CAPTL_INVMT_BSLINE.TOTAL_EST_AM

)

I was able to represent most of the formula in terms of SQL but I have some doubt in converting FORALL function into SQL terms, can you please look into it and add the equivalent FORALL sql in the code below.

Oracle SQL:

SELECT CASE WHEN DW.CAPTL_INVMT_BSLINE.BASELINE_ID=0 THEN 'Current Cost Baseline' WHEN (DW.CAPTL_INVMT_BSLINE.BASELINE_ID=MAX(DW.CAPTL_INVMT_BSLINE.BASELINE_ID)) THEN 'Original Baseline' Else 'Re-baseline' END AS "Baseline text" from DW.CAPTL_INVMT_BSLINE GROUP BY DW.CAPTL_INVMT_BSLINE.BASELINE_ID

-- FORALL - yet to add equivalent foreach function;

Thanks & Regards.

Naveen.

[amitrathi239]

are you not getting desired result without adding forall objects in univers case statement?

If not then add all those objects in group by cla

[Former Member]

Amit,

Thanks for the reply.

Even if i add FORALL objects in the Group By clause of sql statement, the result set in webi & SQL is different.

[amitrathi239]

Forall context is same as group by clause in sql.even I don't think so these objects are required in oracle case statement.create object in universe with case statement and try.

Check attached link for to understand different context

http://www.dagira.com/2010/01/04/calculation-context-part-i-overview/

[Former Member]

Amit,

I am not trying to achieve this FORALL context formula in Universe. I have a requirement to convert all the webi formulas into SQL, so that we can include them in database views.

If possible can you provide me a example by converting webi formulas into SQL by using FORALL & FOREACH.

Thanks & Regards.

Naveen.