Skip to Content
0

Convert WEBI report FOREACH function into Equivalent SQL Logic

Oct 20, 2017 at 03:15 PM

43

avatar image

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 1:

[BAC-AC>20] = If([Project Status] InList("ACTIVE";"COMPLETED" ) And [Completed Work Percentage]>=20 ;[Bdgt At Compltn Am];0) ForEach([EPM ID] )

(Universe Object SQL Definitions:

[Project Status] - DW.IT_FRAMEWORK.PROJECT_STATUS_NM

[Completed Work Percentage] - DW.IT_FRAMEWORK.COMPLETED_WORK_PT
[Bdgt At Compltn Am]-DW.CID_PRFRMNC_VARIANCE.BDGT_AT_COMPLTN_AM

[EPM ID]- DW.IT_FRAMEWORK.EPM_PRJCT_ID

)

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

Oracle SQL:

SELECT CASE WHEN (DW.IT_FRAMEWORK.PROJECT_STATUS_NM IN ('ACTIVE','COMPLETED') AND DW.IT_FRAMEWORK.COMPLETED_WORK_PT >=20 )

THEN DW.CID_PRFRMNC_VARIANCE.BDGT_AT_COMPLTN_AM ELSE 0

END AS "BAC-AC>20"

FROM DW.IT_FRAMEWORK, DW.CID_PRFRMNC_VARIANCE

WHERE

(DW.IT_FRAMEWORK.EPM_PRJCT_ID=DW.CID_PRFRMNC_VARIANCE.EPM_PRJCT_ID(+) )

AND ( DW.IT_FRAMEWORK.SNAPSHOT_DT=DW.CID_PRFRMNC_VARIANCE.SNAPSHOT_DT(+) )

-- FOREACH - yet to add equivalent foreach function

;

Thanks & Regards.

Naveen.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers