PS Query & SQL

TO_CHAR(TO_DATE(B.EFFDT),'J') | | '.' | | B.EFFSEQ

You end up having to create the expression 3 times though:

SELECT A.EMPLID, A.AID_YEAR, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.EFFSEQ, TO_CHAR(B.EFFDT,'YYYY-MM-DD'), B.EFFSEQ
  FROM PS_STDNT_TERM_BDGT A, PS_STDNT_TERM_BDGT B
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_STDNT_TERM_BDGT A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.INSTITUTION = A_ED.INSTITUTION
          AND A.AID_YEAR = A_ED.AID_YEAR
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STRM = A_ED.STRM
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_STDNT_TERM_BDGT A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.INSTITUTION = A_ES.INSTITUTION
          AND A.AID_YEAR = A_ES.AID_YEAR
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STRM = A_ES.STRM
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.INSTITUTION = B.INSTITUTION
     AND A.AID_YEAR = B.AID_YEAR
     AND A.ACAD_CAREER = B.ACAD_CAREER
     AND A.STRM = B.STRM
     AND TO_CHAR(TO_DATE( B.EFFDT),'J') | | '.' | |  B.EFFSEQ = (SELECT MAX(TO_CHAR(TO_DATE( B.EFFDT),'J') | | '.' | |  B.EFFSEQ)
        FROM PS_STDNT_TERM_BDGT C
        WHERE C.EMPLID = A.EMPLID
            AND C.INSTITUTION = A.INSTITUTION
            AND C.AID_YEAR = A.AID_YEAR
            AND C.ACAD_CAREER = A.ACAD_CAREER
            AND C.STRM = A.STRM
            AND TO_CHAR(TO_DATE( B.EFFDT),'J') | | '.' | |  B.EFFSEQ < TO_CHAR(TO_DATE( A.EFFDT),'J') | | '.' | |  A.EFFSEQ))

This is the SQL from a PS Query I have which checks the current row and the immediately previous row for particular values.  Following the SQL are screen shots of the run control record and parameters which I am using.  The term range referred to is coming from the File Parser Conversion Definition table.  Given some of the complexity, we built a view (DU_ADMA_CURPREV) to assist and that SQL follows the screen shots.

Hope this helps you.

SELECT A.EMPLID, A.INSTITUTION, A.ACAD_CAREER, A.STDNT_CAR_NBR, A.ADM_APPL_NBR, A.APPL_PROG_NBR, B.NAME
  FROM PS_SAD_APL_PRG_BND A, PS_SCC_NAMES_QVW B, PS_DU_ADMA_CURPREV C, PS_TERM_TBL F
  WHERE ( A.EMPLID = B.EMPLID
     AND B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_SCC_NAMES_QVW B_ED
        WHERE B.EMPLID = B_ED.EMPLID
          AND B.NAME_TYPE = B_ED.NAME_TYPE
          AND B_ED.EFFDT <= SYSDATE)
     AND B.NAME_TYPE = 'PRI'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND A.ADM_APPL_NBR = C.ADM_APPL_NBR
     AND A.APPL_PROG_NBR = C.APPL_PROG_NBR
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_DU_ADMA_CURPREV C_ED
        WHERE C.EMPLID = C_ED.EMPLID
          AND C.ACAD_CAREER = C_ED.ACAD_CAREER
          AND C.STDNT_CAR_NBR = C_ED.STDNT_CAR_NBR
          AND C.ADM_APPL_NBR = C_ED.ADM_APPL_NBR
          AND C.APPL_PROG_NBR = C_ED.APPL_PROG_NBR
          AND C_ED.EFFDT <= SYSDATE)
    AND C.EFFSEQ =
        (SELECT MAX(C_ES.EFFSEQ) FROM PS_DU_ADMA_CURPREV C_ES
        WHERE C.EMPLID = C_ES.EMPLID
          AND C.ACAD_CAREER = C_ES.ACAD_CAREER
          AND C.STDNT_CAR_NBR = C_ES.STDNT_CAR_NBR
          AND C.ADM_APPL_NBR = C_ES.ADM_APPL_NBR
          AND C.APPL_PROG_NBR = C_ES.APPL_PROG_NBR
          AND C.EFFDT = C_ES.EFFDT)
     AND A.INSTITUTION = :1
     AND A.ACAD_CAREER = :2
     AND C.ADMIT_TERM >= (SELECT D.SCCFP_PSFT_VALUE
  FROM PS_SCCFP_CNVR_VAL D
  WHERE D.SCCFP_CNVR_ID = 10005
     AND D.SCCFP_CNVR_FIELD = 'AUTO_MATRIC_TERM_RANGE'
     AND D.SCCFP_FILE_VALUE = 'START_TERM')
     AND C.ADMIT_TERM <= (SELECT E.SCCFP_PSFT_VALUE
  FROM PS_SCCFP_CNVR_VAL E
  WHERE E.SCCFP_CNVR_ID = 10005
     AND E.SCCFP_CNVR_FIELD = 'AUTO_MATRIC_TERM_RANGE'
     AND E.SCCFP_FILE_VALUE = 'END_TERM')
     AND A.INSTITUTION = :3
     AND C.PROG_ACTION = :4
     AND C.PROG_REASON = :5
     AND C.PREV_PROG_ACTION = :6
     AND C.PREV_PROG_REASON = :7
     AND A.INSTITUTION = F.INSTITUTION
     AND A.ACAD_CAREER = F.ACAD_CAREER
     AND C.ADMIT_TERM = F.STRM
     AND SYSDATE <= F.TERM_BEGIN_DT)

David Ehrlich | Senior Business System Analyst

Duke University | Student Information Services & System (SISS)

david.ehrlich@duke.edu | 919-684-1206

www.sissoffice.duke.edu