I have a long query that works beautifully and returns the results I need. However, when I try to run that query with an INSERT INTO, it throws "ORA-01799: a column may not be outer-joined to a subquery". I've never come across this and haven't been able to find a clear answer when searching. I would appreciate any advice.
I do use subqueries in outer joins in three places. I could probably eliminate one of them by bringing it up into the SELECT and making it a CASE WHEN EXISTS. For the other two, I have no clue. I can't provide any data, and the script is longer than the 20,000 characters allowed, so I am pasting what I hope will be enough for someone to figure it out.
Seriously huge thanks.
INSERT INTO TGUY.PDP_COHORT_RESULTS /* TAKE THIS LINE OUT AND EVERYTHING WORKS. LEAVE IT IN AND GET ORA-01799. */
WITH pdtm AS /* PIDM-Term */
(
SELECT DISTINCT
creg.sfrstcr_pidm AS pidm,
creg.sfrstcr_term_code AS term
FROM
sfrstcr creg
INNER JOIN
ssbsect sect
ON creg.sfrstcr_term_code = sect.ssbsect_term_code
AND creg.sfrstcr_crn = sect.ssbsect_crn
INNER JOIN
scbsupp supp
ON sect.ssbsect_subj_code = supp.scbsupp_subj_code
WHERE
creg.sfrstcr_term_code = &term /* Term selected. */
AND
(
(
creg.sfrstcr_levl_code = 'CR' /* Credit-level courses and only certain grades. */
AND
creg.sfrstcr_grde_code IN ('A', 'B', 'C', 'D', 'F', 'P', 'NP', 'W', 'I')
)
OR
(
creg.sfrstcr_levl_code = 'NC' /* Non-credit course. */
AND
supp.scbsupp_ccsl_code = 'V' /* Credentialed-ish vocational course. */
)
)
AND NOT EXISTS
( /* Eliminate students who had a PCC class before, UNLESS it was a dual enrollment. #1 */
SELECT
1
FROM
sfrstcr creg1
INNER JOIN
ssbsect sect1
ON creg1.sfrstcr_term_code = sect1.ssbsect_term_code
AND creg1.sfrstcr_crn = sect1.ssbsect_crn
INNER JOIN
scbsupp supp1
ON sect1.ssbsect_subj_code = supp1.scbsupp_subj_code
WHERE
creg1.sfrstcr_pidm = creg.sfrstcr_pidm
AND creg1.sfrstcr_term_code < creg.sfrstcr_term_code
AND
(
(
creg1.sfrstcr_levl_code = 'CR'
AND
creg1.sfrstcr_grde_code IN ('A', 'B', 'C', 'D', 'F', 'P', 'NP', 'W', 'I', 'X')
)
OR
(
creg1.sfrstcr_levl_code = 'NC'
AND
supp1.scbsupp_ccsl_code = 'V'
)
)
AND NOT EXISTS
( /* Exclude previous dual enrollments from exception. (Woo! Exception Inception!) #1a */
SELECT
1
FROM
sfrstcr creg2
INNER JOIN
ssbsect sect2
ON creg2.sfrstcr_term_code = sect2.ssbsect_term_code
AND creg2.sfrstcr_crn = sect2.ssbsect_crn
INNER JOIN
stvrsts stat2
ON creg2.sfrstcr_rsts_code = stat2.stvrsts_code
WHERE
creg2.sfrstcr_pidm = creg1.sfrstcr_pidm
AND creg2.sfrstcr_term_code = creg1.sfrstcr_term_code
AND creg2.sfrstcr_levl_code = 'CR'
AND creg2.sfrstcr_credit_hr > 0
AND stat2.stvrsts_incl_sect_enrl = 'Y'
AND sect2.ssbsect_camp_code IN('6')
AND sect2.ssbsect_ssts_code = 'A'
AND NVL(sect2.ssbsect_sess_code, '#') IN('A', 'P', 'O')
)
)
AND NOT EXISTS
( /* Eliminate current dual enrollment. */
SELECT
1
FROM
sfrstcr creg3
INNER JOIN
ssbsect sect3
ON creg3.sfrstcr_term_code = sect3.ssbsect_term_code
AND creg3.sfrstcr_crn = sect3.ssbsect_crn
INNER JOIN
stvrsts stat3
ON creg3.sfrstcr_rsts_code = stat3.stvrsts_code
WHERE
creg3.sfrstcr_pidm = creg.sfrstcr_pidm
AND creg3.sfrstcr_term_code = creg.sfrstcr_term_code
AND creg3.sfrstcr_levl_code = 'CR'
AND creg3.sfrstcr_credit_hr > 0
AND stat3.stvrsts_incl_sect_enrl = 'Y'
AND sect3.ssbsect_camp_code IN('6')
AND sect3.ssbsect_ssts_code = 'A'
AND NVL(sect3.ssbsect_sess_code, '#') IN('A', 'P', 'O')
)
AND NOT EXISTS
( /* Eliminate students still in HS. */
SELECT
1
FROM
sorhsch inhs
WHERE
inhs.sorhsch_pidm = creg.sfrstcr_pidm
AND inhs.sorhsch_dplm_code = 'S'
)
),
/* SELECT COUNT(pidm), COUNT(DISTINCT pidm) FROM pdtm ; */
/* Get the cohort term and date info. */
coht AS /* Cohort */
(
SELECT
pdtm.pidm,
pdtm.term,
'D1' AS ch1,
SUBSTR(pdtm.term, 1, 4)
|| '-'
|| TO_CHAR(TO_NUMBER(SUBSTR(pdtm.term, 3, 2)) + 1) AS cohort,
CASE SUBSTR(pdtm.term, 5, 2)
WHEN '04' THEN 'Fall'
WHEN '01' THEN 'Winter'
WHEN '02' THEN 'Spring'
WHEN '03' THEN 'Summer'
END AS cohort_term,
term.stvterm_start_date AS cohort_term_begin_date,
term.stvterm_end_date AS cohort_term_end_date,
'OPEID' AS institution_id_type,
'00321300' AS institution_id
FROM
pdtm
INNER JOIN
stvterm term
ON pdtm.term = term.stvterm_code
),
/* SELECT COUNT(pidm), COUNT(DISTINCT pidm) FROM coht ; */
/* Get "person" details about students. */
pers AS
(
SELECT DISTINCT
coht.pidm,
coht.term,
coht.ch1,
coht.cohort,
coht.cohort_term,
coht.cohort_term_begin_date,
coht.cohort_term_end_date,
prs1.spbpers_ssn AS ssn,
'' AS itin,
prs1.spbpers_birth_date AS date_of_birth,
CASE
WHEN f_pcc_race_ethn('FEDC', coht.pidm) = 'HIS'
THEN 'H'
WHEN f_pcc_race_ethn('FEDC', coht.pidm) = 'UNK'
THEN 'UNK'
ELSE
'N'
END AS ethnicity,
CASE
WHEN f_pcc_race_ethn('FEDC', coht.pidm) = 'AI'
THEN 'AI'
WHEN f_pcc_race_ethn('FEDC', coht.pidm) = 'AS'
THEN 'AN'
WHEN f_pcc_race_ethn('FEDC', coht.pidm) = 'BAA'
THEN 'B'
WHEN f_pcc_race_ethn('FEDC', coht.pidm) = 'HPI'
THEN 'HP'
WHEN f_pcc_race_ethn('FEDC', coht.pidm) = 'WHI'
THEN 'W'
ELSE
'UNK'
END AS race,
coht.institution_id_type,
coht.institution_id,
CASE prs1.spbpers_lgcy_code /* PDP has 5 values for this. */
WHEN 'N' THEN 'N' /* They also have special instructions for FAFSA info. */
WHEN 'Y' THEN 'B' /* TODO: ask financial aid programmers about FAFSA. Rob Botts or Watasha. */
ELSE NULL /* and how best to meet them. */
END AS first_gen
FROM
coht
INNER JOIN
spbpers prs1
ON coht.pidm = prs1.spbpers_pidm
),
/* SELECT COUNT(pidm), COUNT(DISTINCT pidm) FROM pers ; */
/* Get student name. */
stnm AS
(
SELECT DISTINCT
pers.pidm,
pers.term,
pers.ch1,
pers.cohort,
pers.cohort_term,
pers.cohort_term_begin_date,
pers.cohort_term_end_date,
pers.ssn,
pers.itin,
stid.spriden_id AS student_id,
stid.spriden_first_name AS first_name,
stid.spriden_mi AS middle_name,
stid.spriden_last_name AS last_name,
pers.date_of_birth,
pers.ethnicity,
pers.race,
pers.institution_id_type,
pers.institution_id,
pers.first_gen
FROM
pers
INNER JOIN
spriden stid
ON pers.pidm = stid.spriden_pidm
AND stid.spriden_change_ind IS NULL /* Most recent record will not have changed. */
AND stid.spriden_entity_ind = 'P' /* Only Persons, not vendors. */
),
/* SELECT COUNT(pidm), COUNT(DISTINCT pidm) FROM stnm ; */
/* Get student address. */
stad AS
(
SELECT DISTINCT
stnm.pidm,
stnm.term,
stnm.ch1,
stnm.cohort,
stnm.cohort_term,
stnm.cohort_term_begin_date,
stnm.cohort_term_end_date,
stnm.ssn,
stnm.itin,
stnm.student_id,
stnm.first_name,
stnm.middle_name,
stnm.last_name,
NVL(addr.spraddr_street_line1, 'UK') AS street_line_1,
addr.spraddr_street_line2 AS street_line_2,
NVL(addr.spraddr_city, 'UK') AS city,
NVL(addr.spraddr_stat_code, 'UK') AS state,
addr.spraddr_zip AS zip_code,
DECODE (addr.spraddr_natn_code,
1, 'AF', 2, 'AL', 3, 'DZ', 4, 'AD', 5, 'AO', 172, 'AI', 173, 'AQ', 6, 'AG', 7, 'AR', 174, 'AM',
175, 'AW', 8, 'AU', 9, 'AT', 177, 'AZ', 10, 'BS', 11, 'BH', 12, 'BD', 13, 'BB', 181, 'BY',
14, 'BE', 15, 'BZ', 16, 'BJ', 182, 'BM', 17, 'BT', 18, 'BO', 183, 'BA', 19, 'BW', 184, 'BV',
20, 'BR', 185, 'IO', 21, 'BN', 22, 'BG', 186, 'BF', 24, 'BI', 25, 'KH', 26, 'CM', 27, 'CA',
28, 'CV', 188, 'KY', 29, 'CF', 30, 'TD', 31, 'CL', 32, 'CN', 189, 'CX', 192, 'CC', 34, 'CO',
35, 'KM', 36, 'CG', 193, 'CK', 37, 'CR', 75, 'CI', 196, 'HR', 38, 'CU', 39, 'CY', 40, 'CZ',
41, 'DK', 42, 'DJ', 43, 'DM', 44, 'DO', 45, 'EC', 46, 'EG', 47, 'SV', 48, 'GQ', 197, 'ER',
198, 'EE', 49, 'ET', 200, 'FK', 201, 'FO', 50, 'FJ', 51, 'FI', 52, 'FR', 202, 'GF', 203, 'PF',
204, 'TF', 53, 'GA', 54, 'GM', 206, 'GE', 170, 'DE', 57, 'GH', 207, 'GI', 58, 'GR', 209, 'GL',
59, 'GD', 210, 'GP', 60, 'GU', 61, 'GT', 211, 'GG', 212, 'GN', 213, 'GW', 63, 'GY', 64, 'HT',
214, 'HM', 65, 'HN', 215, 'HK', 66, 'HU', 67, 'IS', 68, 'IN', 69, 'ID', 70, 'IR', 71, 'IQ',
72, 'IE', 218, 'IM', 73, 'IL', 74, 'IT', 76, 'JM', 219, 'SJ', 77, 'JP', 220, 'JE', 78, 'JO',
223, 'KZ', 79, 'KE', 80, 'KI', 83, 'KW', 225, 'KG', 84, 'LA', 226, 'LV', 85, 'LB', 86, 'LS',
87, 'LR', 88, 'LY', 89, 'LI', 227, 'LT', 90, 'LU', 228, 'MO', 229, 'MK', 91, 'MG', 92, 'MW',
93, 'MY', 94, 'MV', 95, 'ML', 96, 'MT', 230, 'MH', 231, 'MQ', 97, 'MR', 98, 'MU', 232, 'YT',
99, 'MX', 233, 'FM', 235, 'MD', 100, 'MC', 101, 'MN', 236, 'ME', 237, 'MS', 102, 'MA', 103, 'MZ',
273, 'MM', 238, 'NA', 104, 'NR', 105, 'NP', 106, 'NL', 240, 'AN', 241, 'NC', 107, 'NZ',
108, 'NI', 109, 'NE', 110, 'NG', 242, 'NU', 243, 'NF', 81, 'KP', 111, 'NO', 112, 'OM',
113, 'PK', 114, 'PA', 115, 'PG', 116, 'PY', 117, 'PE', 118, 'PH', 248, 'PN', 119, 'PL',
120, 'PT', 121, 'QA', 249, 'RE', 122, 'RO', 154, 'RU', 123, 'RW', 250, 'SH', 124, 'KN',
125, 'LC', 251, 'PM', 126, 'VC', 163, 'WS', 127, 'SM', 128, 'ST', 129, 'SA', 130, 'SN',
252, 'RS', 131, 'SC', 132, 'SL', 133, 'SG', 253, 'SK', 254, 'SI', 134, 'SB', 135, 'SO',
136, 'ZA', 255, 'GS', 82, 'KR', 137, 'ES', 138, 'LK', 139, 'SD', 140, 'SR', 257, 'SJ',
141, 'SZ', 142, 'SE', 143, 'CH', 144, 'SY', 33, 'TW', 258, 'TJ', 145, 'TZ', 146, 'TH',
274, 'TL', 147, 'TG', 259, 'TK', 148, 'TO', 149, 'TT', 261, 'MP', 150, 'TN', 151, 'TR',
262, 'TM', 263, 'TC', 152, 'TV', 153, 'UG', 264, 'UA', 155, 'AE', 156, 'GB', 157, 'US',
159, 'UY', 265, 'UZ', 160, 'VU', 266, 'VA', 161, 'VE', 162, 'VN', 267, 'VG', 269, 'WF',
271, 'EH', 164, 'YE', 168, 'ZM', 169, 'ZW',
'UK') AS country,
stnm.date_of_birth,
stnm.ethnicity,
stnm.race,
stnm.institution_id_type,
stnm.institution_id,
stnm.first_gen
FROM
stnm
LEFT JOIN
spraddr addr
ON stnm.pidm = addr.spraddr_pidm
AND stnm.cohort_term_begin_date >= addr.spraddr_from_date
AND stnm.cohort_term_begin_date <= NVL(addr.spraddr_to_date,
TO_DATE('12/31/2099','MM/DD/YYYY'))
AND addr.spraddr_atyp_code = 'MA'
AND NVL(addr.spraddr_status_ind, 'X') != 'I'
AND addr.spraddr_seqno =
( /* To eliminate duplicates. */
SELECT MAX(stadd1.spraddr_seqno)
FROM spraddr stadd1
WHERE stadd1.spraddr_pidm = addr.spraddr_pidm
AND stadd1.spraddr_atyp_code = 'MA'
AND stadd1.spraddr_from_date <= stnm.cohort_term_begin_date
AND NVL(stadd1.spraddr_to_date, TO_DATE('12/31/2099','MM/DD/YYYY'))
>= stnm.cohort_term_begin_date
)
),
/* SELECT COUNT(pidm), COUNT(DISTINCT pidm) FROM stad ; */
/* Get high school info. */
sths AS
(
SELECT DISTINCT
stad.pidm,
stad.term,
stad.ch1,
stad.cohort,
stad.cohort_term,
stad.cohort_term_begin_date,
stad.cohort_term_end_date,
stad.ssn,
stad.itin,
stad.student_id,
stad.first_name,
stad.middle_name,
stad.last_name,
stad.street_line_1,
stad.street_line_2,
stad.city,
stad.state,
stad.zip_code,
stad.country,
stad.date_of_birth,
stad.ethnicity,
stad.race,
stad.institution_id_type,
stad.institution_id,
hisc.hs_completion_status,
hisc.hs_completion_year,
hisc.hs_unweighted_gpa,
hisc.hs_weighted_gpa,
stad.first_gen
FROM
stad
LEFT JOIN
(
SELECT
pidm,
hs_completion_status,
hs_completion_year,
hs_unweighted_gpa,
hs_weighted_gpa
FROM
(
SELECT
hsdt.sorhsch_pidm AS pidm,
CASE hsdt.sorhsch_dplm_code
WHEN 'D' THEN 'D' /* Regular diploma. */
WHEN 'G' THEN 'G' /* GED. */
WHEN 'P' THEN 'A' /* PCC-HS counts as Adult HS Diploma. */
WHEN 'N' THEN 'O' /* Did Not Complete counts as Other. */
END AS hs_completion_status,
EXTRACT(YEAR FROM hsdt.sorhsch_graduation_date) AS hs_completion_year,
hsdt.sorhsch_gpa AS hs_unweighted_gpa,
NULL AS hs_weighted_gpa,
ROW_NUMBER() OVER (PARTITION BY hsdt.sorhsch_pidm
ORDER BY hsdt.sorhsch_graduation_date DESC,
hsdt.sorhsch_activity_date DESC,
hsdt.sorhsch_surrogate_id DESC) AS r
FROM
sorhsch hsdt /* high school data */
)
WHERE
r = 1
) hisc /* high school */
ON stad.pidm = hisc.pidm
),
/* SELECT COUNT(pidm), COUNT(DISTINCT pidm) FROM sths ; */
/* Get past dual enrollment - summer enrollment. */
dese AS
(
SELECT DISTINCT
sths.pidm,
sths.term,
sths.ch1,
sths.cohort,
sths.cohort_term,
sths.cohort_term_begin_date,
sths.cohort_term_end_date,
sths.ssn,
sths.itin,
sths.student_id,
sths.first_name,
sths.middle_name,
sths.last_name,
sths.street_line_1,
sths.street_line_2,
sths.city,
sths.state,
sths.zip_code,
sths.country,
sths.date_of_birth,
sths.ethnicity,
sths.race,
sths.institution_id_type,
sths.institution_id,
sths.hs_completion_status,
sths.hs_completion_year,
sths.hs_unweighted_gpa,
sths.hs_weighted_gpa,
sths.first_gen,
CASE WHEN stde.pidm IS NOT NULL THEN 'DE' END AS dual_summer_enrollment
FROM
sths
LEFT JOIN
(
SELECT
creg4.sfrstcr_pidm AS pidm
FROM
sfrstcr creg4
INNER JOIN
ssbsect sect4
ON creg4.sfrstcr_term_code = sect4.ssbsect_term_code
AND creg4.sfrstcr_crn = sect4.ssbsect_crn
INNER JOIN
stvrsts stat4
ON creg4.sfrstcr_rsts_code = stat4.stvrsts_code
WHERE
creg4.sfrstcr_term_code < &term
AND creg4.sfrstcr_levl_code = 'CR'
AND creg4.sfrstcr_credit_hr > 0
AND stat4.stvrsts_incl_sect_enrl = 'Y'
AND sect4.ssbsect_camp_code IN('6')
AND sect4.ssbsect_ssts_code = 'A'
AND NVL(sect4.ssbsect_sess_code, '#') IN('A', 'P', 'O')
) stde /* student dual enrollment */
ON sths.pidm = stde.pidm
),
More CTEs (none with a subquery) and then a final SELECT statement.