PS Query & SQL

Hello,

I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = '24554411'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
  ORDER BY 2 DESC

I would get rid of ACAD_PROG all together.  Start with STDNT_CAR_TERM and then do outer joins for both TRNS_CRSE_TERM and CLASS_TBL_SE_VW.  I think looking for the active program is taken care of by just grabbing STDNT_CAR_TERM.  

Hello,

I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = '24554411'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
  ORDER BY 2 DESC

I would get rid of ACAD_PROG all together.  Start with STDNT_CAR_TERM and then do outer joins for both TRNS_CRSE_TERM and CLASS_TBL_SE_VW.  I think looking for the active program is taken care of by just grabbing STDNT_CAR_TERM.  

Hello,

I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = '24554411'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
  ORDER BY 2 DESC

I would get rid of ACAD_PROG all together.  Start with STDNT_CAR_TERM and then do outer joins for both TRNS_CRSE_TERM and CLASS_TBL_SE_VW.  I think looking for the active program is taken care of by just grabbing STDNT_CAR_TERM.  

Hello,

I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = '24554411'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
  ORDER BY 2 DESC

I would get rid of ACAD_PROG all together.  Start with STDNT_CAR_TERM and then do outer joins for both TRNS_CRSE_TERM and CLASS_TBL_SE_VW.  I think looking for the active program is taken care of by just grabbing STDNT_CAR_TERM.  

Hello,

I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = '24554411'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
  ORDER BY 2 DESC

Hi Lidia,

Similar to Josh, I would recommend a union. Starting with your baseline query, you can add TRNS_CRSE_DTL like this:

SELECT A.EMPLID, 'Institution', B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = :1
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
UNION
SELECT D.EMPLID, 'Transfer', D.ARTICULATION_TERM, D.COMP_SUBJECT_AREA, D.TRNSFR_EQVLNCY_CMP, D.CRSE_GRADE_OFF
  FROM PS_TRNS_CRSE_DTL D
  WHERE ( D.EMPLID = :1)
  ORDER BY 3 DESC

I would get rid of ACAD_PROG all together.  Start with STDNT_CAR_TERM and then do outer joins for both TRNS_CRSE_TERM and CLASS_TBL_SE_VW.  I think looking for the active program is taken care of by just grabbing STDNT_CAR_TERM.  

Hello,

I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = '24554411'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
  ORDER BY 2 DESC

Hi Lidia,

Similar to Josh, I would recommend a union. Starting with your baseline query, you can add TRNS_CRSE_DTL like this:

SELECT A.EMPLID, 'Institution', B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = :1
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
UNION
SELECT D.EMPLID, 'Transfer', D.ARTICULATION_TERM, D.COMP_SUBJECT_AREA, D.TRNSFR_EQVLNCY_CMP, D.CRSE_GRADE_OFF
  FROM PS_TRNS_CRSE_DTL D
  WHERE ( D.EMPLID = :1)
  ORDER BY 3 DESC

I would get rid of ACAD_PROG all together.  Start with STDNT_CAR_TERM and then do outer joins for both TRNS_CRSE_TERM and CLASS_TBL_SE_VW.  I think looking for the active program is taken care of by just grabbing STDNT_CAR_TERM.  

Hello,

I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = '24554411'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
  ORDER BY 2 DESC

Hi Lidia,

Similar to Josh, I would recommend a union. Starting with your baseline query, you can add TRNS_CRSE_DTL like this:

SELECT A.EMPLID, 'Institution', B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = :1
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
UNION
SELECT D.EMPLID, 'Transfer', D.ARTICULATION_TERM, D.COMP_SUBJECT_AREA, D.TRNSFR_EQVLNCY_CMP, D.CRSE_GRADE_OFF
  FROM PS_TRNS_CRSE_DTL D
  WHERE ( D.EMPLID = :1)
  ORDER BY 3 DESC

I would get rid of ACAD_PROG all together.  Start with STDNT_CAR_TERM and then do outer joins for both TRNS_CRSE_TERM and CLASS_TBL_SE_VW.  I think looking for the active program is taken care of by just grabbing STDNT_CAR_TERM.  

Hello,

I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = '24554411'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
  ORDER BY 2 DESC

Hi Lidia,

Similar to Josh, I would recommend a union. Starting with your baseline query, you can add TRNS_CRSE_DTL like this:

SELECT A.EMPLID, 'Institution', B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = :1
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
UNION
SELECT D.EMPLID, 'Transfer', D.ARTICULATION_TERM, D.COMP_SUBJECT_AREA, D.TRNSFR_EQVLNCY_CMP, D.CRSE_GRADE_OFF
  FROM PS_TRNS_CRSE_DTL D
  WHERE ( D.EMPLID = :1)
  ORDER BY 3 DESC

I would get rid of ACAD_PROG all together.  Start with STDNT_CAR_TERM and then do outer joins for both TRNS_CRSE_TERM and CLASS_TBL_SE_VW.  I think looking for the active program is taken care of by just grabbing STDNT_CAR_TERM.  

Hello,

I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = '24554411'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
  ORDER BY 2 DESC

I would get rid of ACAD_PROG all together.  Start with STDNT_CAR_TERM and then do outer joins for both TRNS_CRSE_TERM and CLASS_TBL_SE_VW.  I think looking for the active program is taken care of by just grabbing STDNT_CAR_TERM.  

Hello,

I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = '24554411'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
  ORDER BY 2 DESC

I would get rid of ACAD_PROG all together.  Start with STDNT_CAR_TERM and then do outer joins for both TRNS_CRSE_TERM and CLASS_TBL_SE_VW.  I think looking for the active program is taken care of by just grabbing STDNT_CAR_TERM.  

Hello,

I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = '24554411'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
  ORDER BY 2 DESC

I would get rid of ACAD_PROG all together.  Start with STDNT_CAR_TERM and then do outer joins for both TRNS_CRSE_TERM and CLASS_TBL_SE_VW.  I think looking for the active program is taken care of by just grabbing STDNT_CAR_TERM.  

Hello,

I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = '24554411'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
  ORDER BY 2 DESC

I would get rid of ACAD_PROG all together.  Start with STDNT_CAR_TERM and then do outer joins for both TRNS_CRSE_TERM and CLASS_TBL_SE_VW.  I think looking for the active program is taken care of by just grabbing STDNT_CAR_TERM.  

Hello,

I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = '24554411'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
  ORDER BY 2 DESC

Hello,

I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
  FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
  WHERE ( A.EMPLID = '24554411'
     AND A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID = C.EMPLID
     AND A.ACAD_CAREER = C.ACAD_CAREER
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
     AND C.INSTITUTION = A.INSTITUTION)
  ORDER BY 2 DESC