PS Query & SQL

I'm trying to do an insert into a UC table.  PS Query select gives me 1398 rows.

This is the SQL from PS Query.

SELECT A.INSTITUTION, A.TRNSFR_SRC_ID, A.COMP_SUBJECT_AREA, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), 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.INSTITUTION = A_ED.INSTITUTION
          AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
          AND A.COMP_SUBJECT_AREA = A_ED.COMP_SUBJECT_AREA
          AND A_ED.EFFDT <= SYSDATE)
     AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
     AND NOT EXISTS (SELECT DISTINCT B.TRNSFR_SRC_ID
  FROM PS_UC_TRNCOMP_ATTR B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
          AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
          AND B.COMP_SUBJECT_AREA = B_ED.COMP_SUBJECT_AREA
          AND B_ED.EFFDT <= A.EFFDT)
     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))

I modified it to do the insert into the UC table and it's running with no errors, but not inserting the data.   ???  Do you guys see something that I don't?   I don't use this table, I'm doing it for a friend.   I was able to update the UC table.  Some records existed and they wanted those values changed. That one worked.

Here is my INSERT.  It's chunking fine.  And I put in a message statement and it's telling me it's inserting 0 rows.  I query the data, nothing is being inserted.  It runs and does nothing.

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.INSTITUTION = A_ED.INSTITUTION
          AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
          AND A.COMP_SUBJECT_AREA = A_ED.COMP_SUBJECT_AREA
          AND A_ED.EFFDT <= SYSDATE)
     AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
     AND NOT EXISTS (SELECT DISTINCT B.TRNSFR_SRC_ID
  FROM PS_UC_TRNCOMP_ATTR B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
          AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
          AND B.COMP_SUBJECT_AREA = B_ED.COMP_SUBJECT_AREA
          AND B_ED.EFFDT <= A.EFFDT)
     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))

I'm trying to do an insert into a UC table.  PS Query select gives me 1398 rows.

This is the SQL from PS Query.

SELECT A.INSTITUTION, A.TRNSFR_SRC_ID, A.COMP_SUBJECT_AREA, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), 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.INSTITUTION = A_ED.INSTITUTION
          AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
          AND A.COMP_SUBJECT_AREA = A_ED.COMP_SUBJECT_AREA
          AND A_ED.EFFDT <= SYSDATE)
     AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
     AND NOT EXISTS (SELECT DISTINCT B.TRNSFR_SRC_ID
  FROM PS_UC_TRNCOMP_ATTR B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
          AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
          AND B.COMP_SUBJECT_AREA = B_ED.COMP_SUBJECT_AREA
          AND B_ED.EFFDT <= A.EFFDT)
     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))

I modified it to do the insert into the UC table and it's running with no errors, but not inserting the data.   ???  Do you guys see something that I don't?   I don't use this table, I'm doing it for a friend.   I was able to update the UC table.  Some records existed and they wanted those values changed. That one worked.

Here is my INSERT.  It's chunking fine.  And I put in a message statement and it's telling me it's inserting 0 rows.  I query the data, nothing is being inserted.  It runs and does nothing.

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.INSTITUTION = A_ED.INSTITUTION
          AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
          AND A.COMP_SUBJECT_AREA = A_ED.COMP_SUBJECT_AREA
          AND A_ED.EFFDT <= SYSDATE)
     AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
     AND NOT EXISTS (SELECT DISTINCT B.TRNSFR_SRC_ID
  FROM PS_UC_TRNCOMP_ATTR B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
          AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
          AND B.COMP_SUBJECT_AREA = B_ED.COMP_SUBJECT_AREA
          AND B_ED.EFFDT <= A.EFFDT)
     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))

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?

I'm trying to do an insert into a UC table.  PS Query select gives me 1398 rows.

This is the SQL from PS Query.

SELECT A.INSTITUTION, A.TRNSFR_SRC_ID, A.COMP_SUBJECT_AREA, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), 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.INSTITUTION = A_ED.INSTITUTION
          AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
          AND A.COMP_SUBJECT_AREA = A_ED.COMP_SUBJECT_AREA
          AND A_ED.EFFDT <= SYSDATE)
     AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
     AND NOT EXISTS (SELECT DISTINCT B.TRNSFR_SRC_ID
  FROM PS_UC_TRNCOMP_ATTR B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
          AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
          AND B.COMP_SUBJECT_AREA = B_ED.COMP_SUBJECT_AREA
          AND B_ED.EFFDT <= A.EFFDT)
     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))

I modified it to do the insert into the UC table and it's running with no errors, but not inserting the data.   ???  Do you guys see something that I don't?   I don't use this table, I'm doing it for a friend.   I was able to update the UC table.  Some records existed and they wanted those values changed. That one worked.

Here is my INSERT.  It's chunking fine.  And I put in a message statement and it's telling me it's inserting 0 rows.  I query the data, nothing is being inserted.  It runs and does nothing.

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.INSTITUTION = A_ED.INSTITUTION
          AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
          AND A.COMP_SUBJECT_AREA = A_ED.COMP_SUBJECT_AREA
          AND A_ED.EFFDT <= SYSDATE)
     AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
     AND NOT EXISTS (SELECT DISTINCT B.TRNSFR_SRC_ID
  FROM PS_UC_TRNCOMP_ATTR B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
          AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
          AND B.COMP_SUBJECT_AREA = B_ED.COMP_SUBJECT_AREA
          AND B_ED.EFFDT <= A.EFFDT)
     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))

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?

I'm trying to do an insert into a UC table.  PS Query select gives me 1398 rows.

This is the SQL from PS Query.

SELECT A.INSTITUTION, A.TRNSFR_SRC_ID, A.COMP_SUBJECT_AREA, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), 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.INSTITUTION = A_ED.INSTITUTION
          AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
          AND A.COMP_SUBJECT_AREA = A_ED.COMP_SUBJECT_AREA
          AND A_ED.EFFDT <= SYSDATE)
     AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
     AND NOT EXISTS (SELECT DISTINCT B.TRNSFR_SRC_ID
  FROM PS_UC_TRNCOMP_ATTR B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
          AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
          AND B.COMP_SUBJECT_AREA = B_ED.COMP_SUBJECT_AREA
          AND B_ED.EFFDT <= A.EFFDT)
     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))

I modified it to do the insert into the UC table and it's running with no errors, but not inserting the data.   ???  Do you guys see something that I don't?   I don't use this table, I'm doing it for a friend.   I was able to update the UC table.  Some records existed and they wanted those values changed. That one worked.

Here is my INSERT.  It's chunking fine.  And I put in a message statement and it's telling me it's inserting 0 rows.  I query the data, nothing is being inserted.  It runs and does nothing.

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.INSTITUTION = A_ED.INSTITUTION
          AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
          AND A.COMP_SUBJECT_AREA = A_ED.COMP_SUBJECT_AREA
          AND A_ED.EFFDT <= SYSDATE)
     AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
     AND NOT EXISTS (SELECT DISTINCT B.TRNSFR_SRC_ID
  FROM PS_UC_TRNCOMP_ATTR B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
          AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
          AND B.COMP_SUBJECT_AREA = B_ED.COMP_SUBJECT_AREA
          AND B_ED.EFFDT <= A.EFFDT)
     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))

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?

I'm trying to do an insert into a UC table.  PS Query select gives me 1398 rows.

This is the SQL from PS Query.

SELECT A.INSTITUTION, A.TRNSFR_SRC_ID, A.COMP_SUBJECT_AREA, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), 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.INSTITUTION = A_ED.INSTITUTION
          AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
          AND A.COMP_SUBJECT_AREA = A_ED.COMP_SUBJECT_AREA
          AND A_ED.EFFDT <= SYSDATE)
     AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
     AND NOT EXISTS (SELECT DISTINCT B.TRNSFR_SRC_ID
  FROM PS_UC_TRNCOMP_ATTR B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
          AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
          AND B.COMP_SUBJECT_AREA = B_ED.COMP_SUBJECT_AREA
          AND B_ED.EFFDT <= A.EFFDT)
     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))

I modified it to do the insert into the UC table and it's running with no errors, but not inserting the data.   ???  Do you guys see something that I don't?   I don't use this table, I'm doing it for a friend.   I was able to update the UC table.  Some records existed and they wanted those values changed. That one worked.

Here is my INSERT.  It's chunking fine.  And I put in a message statement and it's telling me it's inserting 0 rows.  I query the data, nothing is being inserted.  It runs and does nothing.

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.INSTITUTION = A_ED.INSTITUTION
          AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
          AND A.COMP_SUBJECT_AREA = A_ED.COMP_SUBJECT_AREA
          AND A_ED.EFFDT <= SYSDATE)
     AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
     AND NOT EXISTS (SELECT DISTINCT B.TRNSFR_SRC_ID
  FROM PS_UC_TRNCOMP_ATTR B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
          AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
          AND B.COMP_SUBJECT_AREA = B_ED.COMP_SUBJECT_AREA
          AND B_ED.EFFDT <= A.EFFDT)
     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))

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?

I'm trying to do an insert into a UC table.  PS Query select gives me 1398 rows.

This is the SQL from PS Query.

SELECT A.INSTITUTION, A.TRNSFR_SRC_ID, A.COMP_SUBJECT_AREA, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), 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.INSTITUTION = A_ED.INSTITUTION
          AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
          AND A.COMP_SUBJECT_AREA = A_ED.COMP_SUBJECT_AREA
          AND A_ED.EFFDT <= SYSDATE)
     AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
     AND NOT EXISTS (SELECT DISTINCT B.TRNSFR_SRC_ID
  FROM PS_UC_TRNCOMP_ATTR B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
          AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
          AND B.COMP_SUBJECT_AREA = B_ED.COMP_SUBJECT_AREA
          AND B_ED.EFFDT <= A.EFFDT)
     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))

I modified it to do the insert into the UC table and it's running with no errors, but not inserting the data.   ???  Do you guys see something that I don't?   I don't use this table, I'm doing it for a friend.   I was able to update the UC table.  Some records existed and they wanted those values changed. That one worked.

Here is my INSERT.  It's chunking fine.  And I put in a message statement and it's telling me it's inserting 0 rows.  I query the data, nothing is being inserted.  It runs and does nothing.

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.INSTITUTION = A_ED.INSTITUTION
          AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
          AND A.COMP_SUBJECT_AREA = A_ED.COMP_SUBJECT_AREA
          AND A_ED.EFFDT <= SYSDATE)
     AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
     AND NOT EXISTS (SELECT DISTINCT B.TRNSFR_SRC_ID
  FROM PS_UC_TRNCOMP_ATTR B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
          AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
          AND B.COMP_SUBJECT_AREA = B_ED.COMP_SUBJECT_AREA
          AND B_ED.EFFDT <= A.EFFDT)
     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))

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?

I'm trying to do an insert into a UC table.  PS Query select gives me 1398 rows.

This is the SQL from PS Query.

SELECT A.INSTITUTION, A.TRNSFR_SRC_ID, A.COMP_SUBJECT_AREA, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), 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.INSTITUTION = A_ED.INSTITUTION
          AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
          AND A.COMP_SUBJECT_AREA = A_ED.COMP_SUBJECT_AREA
          AND A_ED.EFFDT <= SYSDATE)
     AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
     AND NOT EXISTS (SELECT DISTINCT B.TRNSFR_SRC_ID
  FROM PS_UC_TRNCOMP_ATTR B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
          AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
          AND B.COMP_SUBJECT_AREA = B_ED.COMP_SUBJECT_AREA
          AND B_ED.EFFDT <= A.EFFDT)
     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))

I modified it to do the insert into the UC table and it's running with no errors, but not inserting the data.   ???  Do you guys see something that I don't?   I don't use this table, I'm doing it for a friend.   I was able to update the UC table.  Some records existed and they wanted those values changed. That one worked.

Here is my INSERT.  It's chunking fine.  And I put in a message statement and it's telling me it's inserting 0 rows.  I query the data, nothing is being inserted.  It runs and does nothing.

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.INSTITUTION = A_ED.INSTITUTION
          AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
          AND A.COMP_SUBJECT_AREA = A_ED.COMP_SUBJECT_AREA
          AND A_ED.EFFDT <= SYSDATE)
     AND A.TRNSFR_SRC_ID IN ('16001051','16007436')
     AND NOT EXISTS (SELECT DISTINCT B.TRNSFR_SRC_ID
  FROM PS_UC_TRNCOMP_ATTR B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_UC_TRNCOMP_ATTR B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
          AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
          AND B.COMP_SUBJECT_AREA = B_ED.COMP_SUBJECT_AREA
          AND B_ED.EFFDT <= A.EFFDT)
     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))