This question has been deleted.

This question has been undeleted.

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.

0
Comments

## Add comment