Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Another ORA-01799: a column may not be outer-joined to a subquery

Anne-Marie MatulaMay 17 2024 — edited May 17 2024

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

This post has been answered by Anne-Marie Matula on May 21 2024
Jump to Answer
Comments
Post Details
Added on May 17 2024
3 comments
211 views