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