Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Having issue with ORA 00920. sql works when run seperately

Status
Not open for further replies.

DeShaun

Technical User
Mar 3, 2017
16
US
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
 
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


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


Bill
Lead Application Developer
New York State, USA
 
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top