Skip to Content

Convert WEBI report FOREACH function into Equivalent SQL Logic

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers