Hi,
I googled and searched the forum for something similar to my situation but could not find one. I hope you do not mind another post on the error…
I can run my code and tested it as a script. Output was confirmed by me and the functional area.
But when I attempt to convert to a view I receive the ORA-01799: a column may not be outer-joined to a subquery
I have 3 to resolve and they are all set up the same. Here is one:
-- for athletic scholarship
LEFT JOIN (SELECT sgrchrt_pidm,
sgrchrt_term_code_eff,
stvchrt_desc
FROM saturn.sgrchrt ATS
JOIN saturn.stvchrt AS_LKP
ON ATS.sgrchrt_chrt_code = AS_LKP.stvchrt_code
WHERE
ATS.sgrchrt_chrt_code = 'QASCH'
AND ATS.sgrchrt_active_ind IS NULL
AND ATS.sgrchrt_crea_code IS NULL) ATH_SCHL
ON SGB.sgbstdn_pidm = ATH_SCHL.sgrchrt_pidm
AND ATH_SCHL.sgrchrt_term_code_eff = (SELECT MAX(A2.sgrchrt_term_code_eff) FROM saturn.sgrchrt A2
WHERE A2.sgrchrt_pidm = ATH_SCHL.sgrchrt_pidm
AND A2.sgrchrt_term_code_eff <= SFR.sfrstcr_term_code)
It's a long query but SFR is towards the top of the FROM clause, so here are the references needed for that left join:
SELECT
SGB.sgbstdn_pidm,
SFR.sfrstcr_term_code ,
ATH_SCHL.stvchrt_desc
FROM ouregistrar.sgbstdn_cur_fut_active_mv SGB
CROSS JOIN (SELECT oucustom.utilities.f_current_or_past_main_term cur_term FROM DUAL) TRM
JOIN saturn.spriden SP
ON SGB.sgbstdn_pidm = SP.spriden_pidm
AND SP.spriden_change_ind IS NULL
-- here I am extracting current or future terms
JOIN saturn.sfrstcr SFR
ON SP.spriden_pidm = SFR.sfrstcr_pidm
AND SFR.sfrstcr_term_code >= TRM.cur_term
-- for athletic scholarship
LEFT JOIN (SELECT sgrchrt_pidm,
sgrchrt_term_code_eff,
stvchrt_desc
FROM saturn.sgrchrt ATS
JOIN saturn.stvchrt AS_LKP
ON ATS.sgrchrt_chrt_code = AS_LKP.stvchrt_code
WHERE
ATS.sgrchrt_chrt_code = 'QASCH'
AND ATS.sgrchrt_active_ind IS NULL
AND ATS.sgrchrt_crea_code IS NULL) ATH_SCHL
ON SGB.sgbstdn_pidm = ATH_SCHL.sgrchrt_pidm
AND ATH_SCHL.sgrchrt_term_code_eff = (SELECT MAX(A2.sgrchrt_term_code_eff) FROM saturn.sgrchrt A2
WHERE A2.sgrchrt_pidm = ATH_SCHL.sgrchrt_pidm
AND A2.sgrchrt_term_code_eff <= SFR.sfrstcr_term_code);
That works fine until I attempt to change it into a view with
CREATE OR REPLACE FORCE VIEW OUSHARE.TMW_ATHLETIC_ENROLLMENT_V AS
How can I rewrite that? I thought a CTE but the need to compare to the row's SFR term code was throwing me off…
Thank you for any help!
And I hope your weekend is a good one,
Annie
We are on Oracle Database 19c Enterprise Edition Release 19.0.0.0.0