PS Query & SQL

Hello Dana,

Try the following SQL to see if it does anything (also :

INSERT INTO PS_UC_TRNCOMP_ATTR
  (INSTITUTION, TRNSFR_SRC_ID, COMP_SUBJECT_AREA, EFFDT, TRNSFR_EQVLNCY_CMP, CRSE_ATTR, CRSE_ATTR_VALUE)
SELECT
  A.INSTITUTION,
  A.TRNSFR_SRC_ID,
  A.COMP_SUBJECT_AREA,
  A.EFFDT,
  A.TRNSFR_EQVLNCY_CMP,
  'RD',
  'MC'
FROM PS_TRNSFR_FROM A
WHERE
  A.EFFDT = (
    SELECT MAX(A_ED.EFFDT)
    FROM PS_TRNSFR_FROM A_ED
    WHERE A_ED.INSTITUTION       = A.INSTITUTION
      AND A_ED.TRNSFR_SRC_ID     = A.TRNSFR_SRC_ID
      AND A_ED.COMP_SUBJECT_AREA = A.COMP_SUBJECT_AREA
      AND A_ED.EFFDT            <= TRUNC(SYSDATE)
  )
  AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
  AND NOT EXISTS (
    SELECT 1
    FROM PS_UC_TRNCOMP_ATTR B
    WHERE B.INSTITUTION          = A.INSTITUTION
      AND B.TRNSFR_SRC_ID        = A.TRNSFR_SRC_ID
      AND B.COMP_SUBJECT_AREA    = A.COMP_SUBJECT_AREA
      AND B.TRNSFR_EQVLNCY_CMP   = A.TRNSFR_EQVLNCY_CMP
      AND B.EFFDT = (
        SELECT MAX(B_ED.EFFDT)
        FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B_ED.INSTITUTION       = B.INSTITUTION
          AND B_ED.TRNSFR_SRC_ID     = B.TRNSFR_SRC_ID
          AND B_ED.COMP_SUBJECT_AREA = B.COMP_SUBJECT_AREA
          AND B_ED.EFFDT            <= A.EFFDT
      )
  )

I noticed that you have a DISTINCT in the DOES NOT EXIST subquery.  In an EXISTS or NOT EXISTS subquery, the database only cares whether at least one row matches the condition (TRUE or FALSE) and never looks at the actual values returned by the SELECT list.  I am not saying that it is the cause of the issue.  How is the equation set up?

Hello Dana,

Try the following SQL to see if it does anything (also :

INSERT INTO PS_UC_TRNCOMP_ATTR
  (INSTITUTION, TRNSFR_SRC_ID, COMP_SUBJECT_AREA, EFFDT, TRNSFR_EQVLNCY_CMP, CRSE_ATTR, CRSE_ATTR_VALUE)
SELECT
  A.INSTITUTION,
  A.TRNSFR_SRC_ID,
  A.COMP_SUBJECT_AREA,
  A.EFFDT,
  A.TRNSFR_EQVLNCY_CMP,
  'RD',
  'MC'
FROM PS_TRNSFR_FROM A
WHERE
  A.EFFDT = (
    SELECT MAX(A_ED.EFFDT)
    FROM PS_TRNSFR_FROM A_ED
    WHERE A_ED.INSTITUTION       = A.INSTITUTION
      AND A_ED.TRNSFR_SRC_ID     = A.TRNSFR_SRC_ID
      AND A_ED.COMP_SUBJECT_AREA = A.COMP_SUBJECT_AREA
      AND A_ED.EFFDT            <= TRUNC(SYSDATE)
  )
  AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
  AND NOT EXISTS (
    SELECT 1
    FROM PS_UC_TRNCOMP_ATTR B
    WHERE B.INSTITUTION          = A.INSTITUTION
      AND B.TRNSFR_SRC_ID        = A.TRNSFR_SRC_ID
      AND B.COMP_SUBJECT_AREA    = A.COMP_SUBJECT_AREA
      AND B.TRNSFR_EQVLNCY_CMP   = A.TRNSFR_EQVLNCY_CMP
      AND B.EFFDT = (
        SELECT MAX(B_ED.EFFDT)
        FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B_ED.INSTITUTION       = B.INSTITUTION
          AND B_ED.TRNSFR_SRC_ID     = B.TRNSFR_SRC_ID
          AND B_ED.COMP_SUBJECT_AREA = B.COMP_SUBJECT_AREA
          AND B_ED.EFFDT            <= A.EFFDT
      )
  )

I noticed that you have a DISTINCT in the DOES NOT EXIST subquery.  In an EXISTS or NOT EXISTS subquery, the database only cares whether at least one row matches the condition (TRUE or FALSE) and never looks at the actual values returned by the SELECT list.  I am not saying that it is the cause of the issue.  How is the equation set up?

So you put the DISTINCT on the SELECT in the main part of the SQL?

Hello Dana,

Try the following SQL to see if it does anything (also :

INSERT INTO PS_UC_TRNCOMP_ATTR
  (INSTITUTION, TRNSFR_SRC_ID, COMP_SUBJECT_AREA, EFFDT, TRNSFR_EQVLNCY_CMP, CRSE_ATTR, CRSE_ATTR_VALUE)
SELECT
  A.INSTITUTION,
  A.TRNSFR_SRC_ID,
  A.COMP_SUBJECT_AREA,
  A.EFFDT,
  A.TRNSFR_EQVLNCY_CMP,
  'RD',
  'MC'
FROM PS_TRNSFR_FROM A
WHERE
  A.EFFDT = (
    SELECT MAX(A_ED.EFFDT)
    FROM PS_TRNSFR_FROM A_ED
    WHERE A_ED.INSTITUTION       = A.INSTITUTION
      AND A_ED.TRNSFR_SRC_ID     = A.TRNSFR_SRC_ID
      AND A_ED.COMP_SUBJECT_AREA = A.COMP_SUBJECT_AREA
      AND A_ED.EFFDT            <= TRUNC(SYSDATE)
  )
  AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
  AND NOT EXISTS (
    SELECT 1
    FROM PS_UC_TRNCOMP_ATTR B
    WHERE B.INSTITUTION          = A.INSTITUTION
      AND B.TRNSFR_SRC_ID        = A.TRNSFR_SRC_ID
      AND B.COMP_SUBJECT_AREA    = A.COMP_SUBJECT_AREA
      AND B.TRNSFR_EQVLNCY_CMP   = A.TRNSFR_EQVLNCY_CMP
      AND B.EFFDT = (
        SELECT MAX(B_ED.EFFDT)
        FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B_ED.INSTITUTION       = B.INSTITUTION
          AND B_ED.TRNSFR_SRC_ID     = B.TRNSFR_SRC_ID
          AND B_ED.COMP_SUBJECT_AREA = B.COMP_SUBJECT_AREA
          AND B_ED.EFFDT            <= A.EFFDT
      )
  )

I noticed that you have a DISTINCT in the DOES NOT EXIST subquery.  In an EXISTS or NOT EXISTS subquery, the database only cares whether at least one row matches the condition (TRUE or FALSE) and never looks at the actual values returned by the SELECT list.  I am not saying that it is the cause of the issue.  How is the equation set up?

So you put the DISTINCT on the SELECT in the main part of the SQL?

Hello Dana,

Try the following SQL to see if it does anything (also :

INSERT INTO PS_UC_TRNCOMP_ATTR
  (INSTITUTION, TRNSFR_SRC_ID, COMP_SUBJECT_AREA, EFFDT, TRNSFR_EQVLNCY_CMP, CRSE_ATTR, CRSE_ATTR_VALUE)
SELECT
  A.INSTITUTION,
  A.TRNSFR_SRC_ID,
  A.COMP_SUBJECT_AREA,
  A.EFFDT,
  A.TRNSFR_EQVLNCY_CMP,
  'RD',
  'MC'
FROM PS_TRNSFR_FROM A
WHERE
  A.EFFDT = (
    SELECT MAX(A_ED.EFFDT)
    FROM PS_TRNSFR_FROM A_ED
    WHERE A_ED.INSTITUTION       = A.INSTITUTION
      AND A_ED.TRNSFR_SRC_ID     = A.TRNSFR_SRC_ID
      AND A_ED.COMP_SUBJECT_AREA = A.COMP_SUBJECT_AREA
      AND A_ED.EFFDT            <= TRUNC(SYSDATE)
  )
  AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
  AND NOT EXISTS (
    SELECT 1
    FROM PS_UC_TRNCOMP_ATTR B
    WHERE B.INSTITUTION          = A.INSTITUTION
      AND B.TRNSFR_SRC_ID        = A.TRNSFR_SRC_ID
      AND B.COMP_SUBJECT_AREA    = A.COMP_SUBJECT_AREA
      AND B.TRNSFR_EQVLNCY_CMP   = A.TRNSFR_EQVLNCY_CMP
      AND B.EFFDT = (
        SELECT MAX(B_ED.EFFDT)
        FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B_ED.INSTITUTION       = B.INSTITUTION
          AND B_ED.TRNSFR_SRC_ID     = B.TRNSFR_SRC_ID
          AND B_ED.COMP_SUBJECT_AREA = B.COMP_SUBJECT_AREA
          AND B_ED.EFFDT            <= A.EFFDT
      )
  )

I noticed that you have a DISTINCT in the DOES NOT EXIST subquery.  In an EXISTS or NOT EXISTS subquery, the database only cares whether at least one row matches the condition (TRUE or FALSE) and never looks at the actual values returned by the SELECT list.  I am not saying that it is the cause of the issue.  How is the equation set up?

So you put the DISTINCT on the SELECT in the main part of the SQL?

Hello Dana,

Try the following SQL to see if it does anything (also :

INSERT INTO PS_UC_TRNCOMP_ATTR
  (INSTITUTION, TRNSFR_SRC_ID, COMP_SUBJECT_AREA, EFFDT, TRNSFR_EQVLNCY_CMP, CRSE_ATTR, CRSE_ATTR_VALUE)
SELECT
  A.INSTITUTION,
  A.TRNSFR_SRC_ID,
  A.COMP_SUBJECT_AREA,
  A.EFFDT,
  A.TRNSFR_EQVLNCY_CMP,
  'RD',
  'MC'
FROM PS_TRNSFR_FROM A
WHERE
  A.EFFDT = (
    SELECT MAX(A_ED.EFFDT)
    FROM PS_TRNSFR_FROM A_ED
    WHERE A_ED.INSTITUTION       = A.INSTITUTION
      AND A_ED.TRNSFR_SRC_ID     = A.TRNSFR_SRC_ID
      AND A_ED.COMP_SUBJECT_AREA = A.COMP_SUBJECT_AREA
      AND A_ED.EFFDT            <= TRUNC(SYSDATE)
  )
  AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
  AND NOT EXISTS (
    SELECT 1
    FROM PS_UC_TRNCOMP_ATTR B
    WHERE B.INSTITUTION          = A.INSTITUTION
      AND B.TRNSFR_SRC_ID        = A.TRNSFR_SRC_ID
      AND B.COMP_SUBJECT_AREA    = A.COMP_SUBJECT_AREA
      AND B.TRNSFR_EQVLNCY_CMP   = A.TRNSFR_EQVLNCY_CMP
      AND B.EFFDT = (
        SELECT MAX(B_ED.EFFDT)
        FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B_ED.INSTITUTION       = B.INSTITUTION
          AND B_ED.TRNSFR_SRC_ID     = B.TRNSFR_SRC_ID
          AND B_ED.COMP_SUBJECT_AREA = B.COMP_SUBJECT_AREA
          AND B_ED.EFFDT            <= A.EFFDT
      )
  )

I noticed that you have a DISTINCT in the DOES NOT EXIST subquery.  In an EXISTS or NOT EXISTS subquery, the database only cares whether at least one row matches the condition (TRUE or FALSE) and never looks at the actual values returned by the SELECT list.  I am not saying that it is the cause of the issue.  How is the equation set up?