AnsweredAssumed Answered

NSQL for new portlet help

Question asked by lchung on Mar 24, 2010
Latest reply on Mar 30, 2010 by Dave
Hi,  really appreciate if anyone can help on the nsql below, kept getting errors.   attach is the field list and specifications.  SELECT    
                @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:P.PNAME:PNAME@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.STAGE_CODE:STAGE_CODE@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.STAGE_NAME:STAGE_NAME@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.SCHEDULE_FINISH:FINISH_DATE@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.TOTAL_CAPITAL:TOTAL_CAPITAL@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:CAST(ISNULL(T.SPTD,0) AS NUMERIC(16,2)):SPTD@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:CAST(ISNULL(F.REMAINING_CAPITAL_BUDGET,0) AS NUMERIC(16,2)):REMAINING_CAPITAL_BUDGET@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.STATUS_DATE:STATUS_DATE@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.OVERALL_STATUS:OVERALL_STATUS@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.RISK:RISK@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.SCHEDULE:SCHEDULE@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.SCOPE:SCOPE@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.RESOURCE:RESOURCE@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.BUDGET:BUDGET@FROM
INV.NAME PNAME,
INV.STAGE_CODE,
STAGE.NAME STAGE_NAME,
INV.SCHEDULE_FINISH,
ODF.scg_capinlb+ODF.scg_capexlb+ODF.scg_cpswlic+ODF.scg_cap_hw+ODF.scg_cpemcst+ODF.scg_cpnlbot+ODF.sdge_cpinlb+ODF.sdge_cpexlb+ODF.sdge_cpswlc+ODF.sdge_cp_hw+ODF.sdge_cpemcs+ODF.sdge_cpnlbo TOTAL_CAPITAL,
ACT_CAP_COST.SPTD,
(ODF.scg_capinlb+ODF.scg_capexlb+ODF.scg_cpswlic+ODF.scg_cap_hw+ODF.scg_cpemcst+ODF.scg_cpnlbot+ODF.sdge_cpinlb+ODF.sdge_cpexlb+ODF.sdge_cpswlc+ODF.sdge_cp_hw+ODF.sdge_cpemcs+ODF.sdge_cpnlbo)-SPTD REMAINING_CAPITAL_BUDGET,
ODF.SRE_STATDT,
ODF.SRE_STAT,
ODF.SRE_RISK,
ODF.SRE_SCHED,
ODF.SRE_SCOPE,
ODF.SRE_RES,
ODF.SRE_BUDGFROM
NIKU.FIN_PLANS FP
JOIN NIKU.FIN_COST_PLAN_DETAILS FD ON FP.ID = FD.PLAN_ID
JOIN NIKU.PRCHARGECODE CHRG ON CHRG.PRID = FD.PRCHARGECODE_ID
JOIN NIKU.ODF_SSL_CST_DTL_COST CST_DTL   ON FD.ID = CST_DTL.PRJ_OBJECT_ID
WHERE   FP.IS_PLAN_OF_RECORD = 1
AND FP.STATUS_CODE = 'APPROVED'
AND PLAN_TYPE_CODE = 'BUDGET'
GROUP BY FP.OBJECT_ID,FP.TOTAL_COST ) BUDGET ON BUDGET.OBJECT_ID = INV.IDFROM
NIKU.FIN_PLANS FP
JOIN NIKU.FIN_COST_PLAN_DETAILS FD ON FP.ID = FD.PLAN_ID
JOIN NIKU.PRCHARGECODE CHRG ON CHRG.PRID = FD.PRCHARGECODE_ID
JOIN NIKU.ODF_SSL_CST_DTL_COST CST_DTL   ON FD.ID = CST_DTL.PRJ_OBJECT_ID
WHERE   FP.IS_PLAN_OF_RECORD = 1
AND PLAN_TYPE_CODE = 'FORECAST'
GROUP BY FP.OBJECT_ID,FP.CREATED_DATE,FP.LAST_UPDATED_DATE,FP.TOTAL_COST) PLANNED ON PLANNED.OBJECT_ID = INV.IDLEFT JOIN ( SELECT   INV.ID,
SUM(CASE   WHEN YEAR(TRANSDATE) = YEAR(GETDATE()) AND CHRG.PRNAME NOT LIKE '%O&M' THEN (TOTALCOST) ELSE 0 END) SYTD,
SUM(CASE   WHEN   CHRG.PRNAME NOT LIKE '%O&M' THEN TOTALCOST ELSE 0 END ) SPTD
FROM NIKU.PPA_WIP W
JOIN NIKU.PPA_WIP_VALUES WV ON W.TRANSNO = WV.TRANSNO AND CURRENCY_TYPE = 'REPORTING' AND MONTH_BEGIN

Outcomes