Re: SQL not running

["Lu, Clint" <clint.lu@CAREMARK.COM>]

Manuel,

I guess the problem that the SQL statement could not pick any row from
running from SQRW was date criteria. If your oracle NLS_DATE_FORMAT in
registry was set up as "DD-MON-YY", then it picked all the rows you expected
but from SQRW stand point if NLS_DATE_FORMAT in INI file was set up as
"DD-MON-YYYY", then it would not pick up any row. Try to change '15-OCT-01'
to '15-OCT-2001' and then run it from SQRW to see if it picks up rows. Hope
this help.




Clint Lu
Sr. Programmer/Analyst
Caremark Rx, Inc.
Phone: (847) 559-4717
Fax    : (847) 559-4840


-----Original Message-----
From: Manuel Basil Arakkal [mailto:manuelbasil@HOTMAIL.COM]
Sent: Wednesday, October 31, 2001 12:22 AM
To: SQR-USERS@list.iex.net
Subject: SQL not running


I have the following SQL statement in one of my SQR programs. This SQL is
working perfectly well, picking up hundreds of rows, when I run it in Oracle
SQL Plus. However, when I run it using sqrw.exe or from PeopleSoft, the SQL
does not pick up any row. I just get the show statements displayed. I have
set all the allmaxes.max variables to the highest values permissible. Can
any one of you please help me.

BEGIN-PROCEDURE GET-SIP-DATA

Show 'Begin SIP Data'

Begin-SELECT
A.EMPLID
PD.NAME
A2.BUSINESS_TITLE
A2.HIRE_DT
A.EMPL_STATUS
A.ANNUAL_RT
A.CURRENCY_CD
S.CP_ANNUAL_SAL
S.CP_PRORATE_SAL
S.CP_PRIOR_SIP
S.CP_STK_OPTN_MULTPR
S.CP_SIP_CONTRIB_RT
S.CP_WEIGH_VALUE
S.CP_ANNUAL_REC
S.CP_PRORATE_REC
S.CP_CURR_PROP
MGR.NAME
        Show &A.EMPLID
        DO WRITE-ROWS
FROM PS_JOB A, PS_CP_JOB A1, PS_EMPLOYMENT A2, PS_PERSONAL_DATA PD,
  PS_CP_SIP_SETUP SETUP, PS_CP_SIP S
  ,PSTREENODE TR, PSTREENODE DP
  , PS_DEPT_TBL DEPT
  , PS_PERSONAL_DATA MGR
WHERE A.EMPLID = A1.EMPLID
  AND A.EMPL_RCD# = A1.EMPL_RCD#
  AND A.EFFDT = A1.EFFDT
  AND A.EFFSEQ = A1.EFFSEQ
  AND A.EMPLID = PD.EMPLID
  AND A.EFFDT =  (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
           WHERE A.EMPLID = A_ED.EMPLID
             AND A.EMPL_RCD# = A_ED.EMPL_RCD#
             AND A_ED.EFFDT <= SYSDATE)
  AND A.EFFSEQ =
           (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
           WHERE A.EMPLID = A_ES.EMPLID
             AND A.EMPL_RCD# = A_ES.EMPL_RCD#
             AND A.EFFDT = A_ES.EFFDT)
            AND A.EMPLID = A2.EMPLID
  AND A.EMPL_STATUS IN ('A','P')
  AND A1.CP_SIP_ELIG = 'Y'
  AND PD.PER_STATUS = 'E'
  AND SETUP.CP_SIP = 'SIP'
  AND SETUP.EFFDT = (SELECT MAX(S1.EFFDT) FROM PS_CP_SIP_SETUP S1
              WHERE S1.CP_SIP = SETUP.CP_SIP
              AND A.EFFDT <= SYSDATE)
  AND SETUP.EFF_STATUS = 'A'
  AND ((A2.HIRE_DT <= SETUP.CP_NEWELGBLT_DT and
       A2.rehire_dt is null) or (A2.rehire_dt is not null and
     A2.rehire_dt <= SETUP.CP_NEWELGBLT_DT))
     AND A.EMPLID = S.EMPLID
     AND  A.DEPTID = DP.TREE_NODE
     AND
(( TR.SETID  = 'CPSTD'   AND       TR.TREE_NAME='DEPT_SECURITY'
AND       TR.EFFDT= '15-OCT-01'   AND    TR.TREE_LEVEL_NUM = 3
AND TR.SETID = DP.SETID AND TR.TREE_NAME = DP.TREE_NAME
AND TR.EFFDT = DP.EFFDT AND DP.TREE_LEVEL_NUM >
TR.TREE_LEVEL_NUM  AND DP.TREE_NODE_NUM BETWEEN TR.TREE_NODE_NUM AND
TR.TREE_NODE_NUM_END)
OR  ( TR.SETID  = 'CPSTD'   AND   TR.TREE_NAME='DEPT_SECURITY'
AND       TR.EFFDT= '15-OCT-01'
AND TR.TREE_LEVEL_NUM < 3
AND TR.SETID = DP.SETID AND TR.TREE_NAME = DP.TREE_NAME
AND TR.EFFDT = DP.EFFDT AND DP.TREE_LEVEL_NUM >
TR.TREE_LEVEL_NUM  AND DP.TREE_NODE_NUM BETWEEN TR.TREE_NODE_NUM AND
TR.TREE_NODE_NUM_END
AND DP.PARENT_NODE_NUM = TR.TREE_NODE_NUM))
   AND TR.TREE_NODE = DEPT.DEPTID
    AND TR.SETID = DEPT.SETID
    AND DEPT.EFFDT = (SELECT MAX(DEPT1.EFFDT) FROM PS_DEPT_TBL DEPT1
     WHERE DEPT1.DEPTID = DEPT.DEPTID
     AND DEPT1.SETID = DEPT.SETID
     AND DEPT1.EFFDT <= SYSDATE)
    AND DEPT.MANAGER_ID = MGR.EMPLID
GROUP BY  MGR.NAME,
TR.TREE_NODE,  S.CP_STK_OPTN_MULTPR,  A.EMPLID, PD.NAME, A2.BUSINESS_TITLE,
A2.HIRE_DT, A.EMPL_STATUS ,  A.ANNUAL_RT,  A.CURRENCY_CD,  S.CP_ANNUAL_SAL,
S.CP_PRORATE_SAL,  S.CP_PRIOR_SIP,  S.CP_SIP_CONTRIB_RT,  S.CP_WEIGH_VALUE,
S.CP_ANNUAL_REC,  S.CP_PRORATE_REC,  S.CP_CURR_PROP
End-SELECT



Show 'End SIP Data'

END-PROCEDURE GET-SIP-DATA

Manuel

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp