Return: 936 - ORA-00936: missing expression

Toad for Oracle
1

Hi,I have a query which runs successfully in Toad , but when i execute the same through “DO SELECT” step in peoplesoft i get the below error.

“Return: 936 - ORA-00936: missing expression”

This is my code

SELECT BUSINESS_UNIT
, PROJECT_ID
, INTEGRATION_TMPL
, NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘ACT’
AND ACCOUNTING_DT < %Bind(ACCOUNTING_DT_FROM) THEN SUMMED_AMTS END)
, 0) - NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘CLS’
AND ACCOUNTING_DT < %Bind(ACCOUNTING_DT_FROM) THEN SUMMED_AMTS END)
, 0) - NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘EXP’
AND ACCOUNTING_DT < %Bind(ACCOUNTING_DT_FROM) THEN SUMMED_AMTS END)
, 0) PROJ_BAL
,NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘ACT’
AND ACCOUNTING_DT BETWEEN (%Bind(ACCOUNTING_DT_FROM)) AND (%Bind(ACCOUNTING_DT_TO)) THEN SUMMED_AMTS END), 0) ACTUAL, NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘CLS’
AND ACCOUNTING_DT BETWEEN (%Bind(ACCOUNTING_DT_FROM)) AND (%Bind(ACCOUNTING_DT_TO)) THEN SUMMED_AMTS END), 0) CLOSE, NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘EXP’
AND ACCOUNTING_DT BETWEEN (%Bind(ACCOUNTING_DT_FROM)) AND (%Bind(ACCOUNTING_DT_TO)) THEN SUMMED_AMTS END), 0)EXPENSE, NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘ACT’
AND ACCOUNTING_DT <= %Bind(ACCOUNTING_DT_TO) THEN SUMMED_AMTS END), 0) - NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘CLS’
AND ACCOUNTING_DT <= %Bind(ACCOUNTING_DT_TO) THEN SUMMED_AMTS END), 0) - NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘EXP’
AND ACCOUNTING_DT <= %Bind(ACCOUNTING_DT_TO) THEN SUMMED_AMTS END), 0) PROJ_BAL_END
FROM (
SELECT A.BUSINESS_UNIT
, A.PROJECT_ID
, A.INTEGRATION_TMPL
, SUM(B.RESOURCE_AMOUNT)SUMMED_AMTS
, B.ANALYSIS_TYPE
, B.ACCOUNTING_DT
FROM sysadm.PS_PROJECT A
, sysadm.PS_PROJ_RESOURCE B
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.PROJECT_ID = B.PROJECT_ID
AND A.BUSINESS_UNIT = ‘PCCAP’
AND A.PROJECT_TYPE IN (‘CAPTL’,‘CSP’,‘VEND’)
AND B.ACCOUNT IN (‘1625’,’ ')
AND B.ANALYSIS_TYPE <> ‘BUD’
AND A.INTEGRATION_TMPL IN (
SELECT business_unit
FROM SYSADM.PS_FZPC_ROLFWD_RUN
WHERE OPRID = %OperatorId
AND RUN_CNTL_ID = %RunControl
AND ACCOUNTING_DT_FROM = %Bind(ACCOUNTING_DT_FROM)
AND ACCOUNTING_DT_TO = %Bind(ACCOUNTING_DT_TO)
UNION
SELECT BUSINESS_UNIT
FROM SYSADM.PS_BUS_UNIT_GL_VW
WHERE (
SELECT COUNT(business_unit)
FROM sysadm.PS_FZPC_ROLFWD_RUN
WHERE OPRID = %OperatorId
AND RUN_CNTL_ID = %RunControl
AND ACCOUNTING_DT_FROM = %Bind(ACCOUNTING_DT_FROM)
AND ACCOUNTING_DT_TO = %Bind(ACCOUNTING_DT_TO) ) = 0 )
GROUP BY A.BUSINESS_UNIT, A.PROJECT_ID, A.INTEGRATION_TMPL, B.ANALYSIS_TYPE, B.ACCOUNTING_DT)
GROUP BY BUSINESS_UNIT, PROJECT_ID, INTEGRATION_TMPL;

Any idea what am i missing?

2

“%Bind” should not be here!

Change to Bind.

As well as other “%” signs (%OperatorId, %RunControl). Here is corrected code


SELECT BUSINESS_UNIT,
         PROJECT_ID,
         INTEGRATION_TMPL,
           NVL (SUM (CASE WHEN ANALYSIS_TYPE = 'ACT' AND ACCOUNTING_DT < Bind (ACCOUNTING_DT_FROM) THEN SUMMED_AMTS END), 0)
         - NVL (SUM (CASE WHEN ANALYSIS_TYPE = 'CLS' AND ACCOUNTING_DT < Bind (ACCOUNTING_DT_FROM) THEN SUMMED_AMTS END), 0)
         - NVL (SUM (CASE WHEN ANALYSIS_TYPE = 'EXP' AND ACCOUNTING_DT < Bind (ACCOUNTING_DT_FROM) THEN SUMMED_AMTS END), 0)
           PROJ_BAL,
         NVL (SUM (CASE WHEN ANALYSIS_TYPE = 'ACT' AND ACCOUNTING_DT BETWEEN (Bind (ACCOUNTING_DT_FROM)) AND (Bind (ACCOUNTING_DT_TO)) THEN SUMMED_AMTS END), 0)
           ACTUAL,
         NVL (SUM (CASE WHEN ANALYSIS_TYPE = 'CLS' AND ACCOUNTING_DT BETWEEN (Bind (ACCOUNTING_DT_FROM)) AND (Bind (ACCOUNTING_DT_TO)) THEN SUMMED_AMTS END), 0)
           CLOSE,
         NVL (SUM (CASE WHEN ANALYSIS_TYPE = 'EXP' AND ACCOUNTING_DT BETWEEN (Bind (ACCOUNTING_DT_FROM)) AND (Bind (ACCOUNTING_DT_TO)) THEN SUMMED_AMTS END), 0)
           EXPENSE,
           NVL (SUM (CASE WHEN ANALYSIS_TYPE = 'ACT' AND ACCOUNTING_DT <= Bind (ACCOUNTING_DT_TO) THEN SUMMED_AMTS END), 0)
         - NVL (SUM (CASE WHEN ANALYSIS_TYPE = 'CLS' AND ACCOUNTING_DT <= Bind (ACCOUNTING_DT_TO) THEN SUMMED_AMTS END), 0)
         - NVL (SUM (CASE WHEN ANALYSIS_TYPE = 'EXP' AND ACCOUNTING_DT <= Bind (ACCOUNTING_DT_TO) THEN SUMMED_AMTS END), 0)
           PROJ_BAL_END
    FROM (  SELECT A.BUSINESS_UNIT,
                   A.PROJECT_ID,
                   A.INTEGRATION_TMPL,
                   SUM (B.RESOURCE_AMOUNT) SUMMED_AMTS,
                   B.ANALYSIS_TYPE,
                   B.ACCOUNTING_DT
              FROM sysadm.PS_PROJECT A, sysadm.PS_PROJ_RESOURCE B
             WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
               AND A.PROJECT_ID = B.PROJECT_ID
               AND A.BUSINESS_UNIT = 'PCCAP'
               AND A.PROJECT_TYPE IN ('CAPTL',
                                      'CSP',
                                      'VEND')
               AND B.ACCOUNT IN ('1625', ' ')
               AND B.ANALYSIS_TYPE <> 'BUD'
               AND A.INTEGRATION_TMPL IN (SELECT business_unit
                                            FROM SYSADM.PS_FZPC_ROLFWD_RUN
                                           WHERE OPRID = OperatorId
                                             AND RUN_CNTL_ID = RunControl
                                             AND ACCOUNTING_DT_FROM = Bind (ACCOUNTING_DT_FROM)
                                             AND ACCOUNTING_DT_TO = Bind (ACCOUNTING_DT_TO)
                                          UNION
                                          SELECT BUSINESS_UNIT
                                            FROM SYSADM.PS_BUS_UNIT_GL_VW
                                           WHERE (SELECT COUNT (business_unit)
                                                    FROM sysadm.PS_FZPC_ROLFWD_RUN
                                                   WHERE OPRID = OperatorId
                                                     AND RUN_CNTL_ID = RunControl
                                                     AND ACCOUNTING_DT_FROM = Bind (ACCOUNTING_DT_FROM)
                                                     AND ACCOUNTING_DT_TO = Bind (ACCOUNTING_DT_TO)) = 0)
          GROUP BY A.BUSINESS_UNIT,
                   A.PROJECT_ID,
                   A.INTEGRATION_TMPL,
                   B.ANALYSIS_TYPE,
                   B.ACCOUNTING_DT)
GROUP BY BUSINESS_UNIT,
         PROJECT_ID,
         INTEGRATION_TMPL;