问题描述
我有一个很长的查询，它工作得很漂亮，并返回我需要的结果。但是，当我尝试使用INSERT init运行该查询时，它会抛出 “ORA-01799: 列可能不是外部连接的子查询”。我从来没有遇到过这个问题，在搜索时也找不到明确的答案。我将不胜感激任何建议。
我确实在三个地方使用外部连接中的子查询。我可能可以通过将其引入SELECT并使其成为存在的案例来消除其中之一。对于另外两个，我不知道。我不能提供任何数据，脚本比允许的20,000个字符长，所以我粘贴了我希望足以让某人弄清楚的东西。
非常感谢。
更多的cte (没有子查询)，然后是最终的SELECT语句。
我确实在三个地方使用外部连接中的子查询。我可能可以通过将其引入SELECT并使其成为存在的案例来消除其中之一。对于另外两个，我不知道。我不能提供任何数据，脚本比允许的20,000个字符长，所以我粘贴了我希望足以让某人弄清楚的东西。
非常感谢。
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
),
更多的cte (没有子查询)，然后是最终的SELECT语句。
专家解答
当使用old-style ( ) outer join时，您不能外部联接到子查询，但是当使用left join语法时，您可以:
但是，当您在插入中使用它时，转换过程中的某些内容会导致限制重新出现:
注意: 此示例在19.10上; 这是在21c中解决的:
因此，如果您需要此发言以支持获得后端口。我无法确定哪个错误/增强请求是此修复程序的来源。
要更改您现有的查询以避免此问题，实际上我们需要看到:
-完整查询
-示例数据 (创建表插入到)
-查询功能的描述
您可以将所有这些放在实时SQL脚本中并共享。
create table t1 (
c1 int
);
create table t2 (
c1 int, c2 int
);
insert into t1 values ( 1 );
insert into t1 values ( 2 );
insert into t2 values ( 1, 1 );
insert into t2 values ( 2, 2 );
commit;
select * from t1, t2
where t2.c1 (+) = t1.c1
and t2.c1 (+) = ( select 1 from dual );
ORA-01799: a column may not be outer-joined to a subquery
select * from t1
left join t2
on t2.c1 = t1.c1
and t2.c1 = ( select 1 from dual );
C1 C1 C2
1 1 1
2 但是，当您在插入中使用它时，转换过程中的某些内容会导致限制重新出现:
create table t3 ( c1 int, c2 int ); insert into t3 select t1.c1, t2.c2 from t1 left join t2 on t2.c1 = t1.c1 and t2.c1 = ( select 1 from dual ); ORA-01799: a column may not be outer-joined to a subquery
注意: 此示例在19.10上; 这是在21c中解决的:
select banner from v$version;
BANNER
Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
insert into t3
select t1.c1, t2.c2 from t1
left join t2
on t2.c1 = t1.c1
and t2.c1 = ( select 1 from dual );
select * from t3;
C1 C2
1 1
2 因此，如果您需要此发言以支持获得后端口。我无法确定哪个错误/增强请求是此修复程序的来源。
要更改您现有的查询以避免此问题，实际上我们需要看到:
-完整查询
-示例数据 (创建表插入到)
-查询功能的描述
您可以将所有这些放在实时SQL脚本中并共享。
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
