Having issue with ORA 00920. sql works when run seperately

[DeShaun]

select distinct spriden_id, spriden_last_name, Spriden_first_name,
Sfrstcr_term_code, SFRSTCR_CRN, SFRSTCR_GRDE_CODE, GRDE_CODE_FINAL
from spriden, sfrstcr
left join (select shrtckn_pidm, SHRTCKG_GRDE_CODE_FINAL as GRDE_CODE_FINAL
from shrtckn, shrtckg
where SHRTCKN_PIDM = SHRTCKG_PIDM
and shrtckg_term_code = shrtckn_term_code
and shrtckn_seq_no = shrtckg_tckn_seq_no
and shrtckg_seq_no =
(select max(shrtckg_seq_no) from shrtckg
where shrtckg_pidm = shrtckn_pidm
and shrtckg_term_code = shrtckn_term_code
and shrtckg_tckn_seq_no = shrtckn_seq_no)
and shrtckn_subj_code like ('S%')
and not ("SHRTCKN_CRSE_NUMB" LIKE '%L' OR "SHRTCKN_CRSE_NUMB" LIKE '%R')
and shrtckn_term_code = '201901') on GRDE_CODE_FINAL.pidm
where spriden_id like ('%900%')
and Sfrstcr_term_code = &term
and spriden_pidm = sfrstcr_pidm

 

[johnherman]

I would try qualifying all your variables with the table name or a pseudonym of the table name from which they are being taken.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[Beilstwh]

As far as I can understand your query, try the followin


Bill
Lead Application Developer
New York State, USA

 

[Beilstwh]

WITH
    Mytable
    AS
        (SELECT Shrtckn_pidm, Shrtckg_grde_code_final AS Grde_code_final
           FROM Shrtckn, Shrtckg
          WHERE     Shrtckn_pidm = Shrtckg_pidm
                AND Shrtckg_term_code = Shrtckn_term_code
                AND Shrtckn_seq_no = Shrtckg_tckn_seq_no
                AND Shrtckg_seq_no =
                    (SELECT MAX (Shrtckg_seq_no)
                       FROM Shrtckg
                      WHERE     Shrtckg_pidm = Shrtckn_pidm
                            AND Shrtckg_term_code = Shrtckn_term_code
                            AND Shrtckg_tckn_seq_no = Shrtckn_seq_no)
                AND Shrtckn_subj_code LIKE ('S%')
                AND NOT (   "SHRTCKN_CRSE_NUMB" LIKE '%L'
                         OR "SHRTCKN_CRSE_NUMB" LIKE '%R')
                AND Shrtckn_term_code = '201901')
SELECT DISTINCT Spriden_id,
                Spriden_last_name,
                Spriden_first_name,
                Sfrstcr_term_code,
                Sfrstcr_crn,
                Sfrstcr_grde_code,
                Grde_code_final
  FROM Spriden,
       Sfrstcr
       LEFT JOIN My_table ON Grde_code_final = pidm
 WHERE     Spriden_id LIKE ('%900%')
       AND Sfrstcr_term_code = &term
       AND Spriden_pidm = Sfrstcr_pidm

My main question is the string
LEFT JOIN My_table ON Grde_code_final = pidm
The Grde_code_final is a returned column from the my_table sub select. However I'm guessing on the pidm. You used the following code

on GRDE_CODE_FINAL.pidm

Where an left join must be followed by the join. You have "on GRDE_CODE_FINAL.pidm", which is not a legal column in your select

Bill
Lead Application Developer
New York State, USA