3. [Oracle] Invalid identifier error with a subquery

[Oracle] Invalid identifier error with a subquery

Fixed your formatting. You have invalid code in the section I commented. It doesn't do anything.

FROM SSBSECT INNER JOIN SCBCRSE ON SSBSECT_SUBJ_CODE = SCBCRSE_SUBJ_CODE AND SSBSECT_CRSE_NUMB = SCBCRSE_CRSE_NUMB INNER JOIN SFRSTCR ON SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE AND SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN INNER JOIN STVRSTS ON SFRSTCR.SFRSTCR_RSTS_CODE = STVRSTS.STVRSTS_CODE INNER JOIN SPRIDEN ON SFRSTCR.SFRSTCR_PIDM = SPRIDEN.SPRIDEN_PIDM INNER JOIN SGBSTDN ON SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM AND SGBSTDN_TERM_CODE_EFF <= SSBSECT.SSBSECT_TERM_CODE INNER JOIN ( SELECT MAX(SGBSTDN_TERM_CODE_EFF) AS SGBSTDN_TERM_CODE_EFF , SGBSTDN_PIDM FROM SSBSECT INNER JOIN SFRSTCR ON SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE AND SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN INNER JOIN SGBSTDN ON SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM AND SGBSTDN_TERM_CODE_EFF <= SSBSECT.SSBSECT_TERM_CODE WHERE SSBSECT.SSBSECT_TERM_CODE = '201750' AND SSBSECT.SSBSECT_SUBJ_CODE = 'PHY' GROUP BY SGBSTDN_PIDM ) xSGBSTDN ON xSGBSTDN.SGBSTDN_TERM_CODE_EFF = SGBSTDN.SGBSTDN_TERM_CODE_EFF AND xSGBSTDN.SGBSTDN_PIDM = SGBSTDN.SGBSTDN_PIDM INNER JOIN SPBPERS ON SPBPERS.SPBPERS_PIDM = SFRSTCR.SFRSTCR_PIDM LEFT JOIN STVETHN ON STVETHN.STVETHN_CODE = SPBPERS.SPBPERS_ETHN_CODE LEFT JOIN GORVISA ON GORVISA.GORVISA_PIDM = SFRSTCR.SFRSTCR_PIDM LEFT JOIN SPRADDR ON SPRADDR.SPRADDR_PIDM = SFRSTCR.SFRSTCR_PIDM AND SPRADDR.SPRADDR_ATYP_CODE = 'AA' AND SPRADDR.SPRADDR_TO_DATE IS NULL AND SPRADDR.SPRADDR_STATUS_IND IS NULL LEFT JOIN ( SELECT spraddr.SPRADDR_PIDM AS xspraddr_pidm , MAX(spraddr.SPRADDR_SEQNO) AS xspraddr_seqno FROM spraddr WHERE spraddr.SPRADDR_ATYP_CODE = 'AA' AND spraddr.SPRADDR_TO_DATE IS NULL AND spraddr.SPRADDR_STATUS_IND IS NULL GROUP BY spraddr.SPRADDR_PIDM ) xSPRADDR ON xSPRADDR.xspraddr_pidm = SPRADDR.SPRADDR_PIDM AND xSPRADDR.xspraddr_seqno = SPRADDR.SPRADDR_SEQNO LEFT JOIN ( SELECT * FROM SPRTELE INNER JOIN ( SELECT MAX(SPRTELE.SPRTELE_SEQNO) AS SPRTELE_SEQNO , SPRTELE.SPRTELE_PIDM AS SPRTELE_PIDM FROM SPRTELE WHERE SPRTELE.SPRTELE_TELE_CODE IN ('CE', 'AA') AND ( SPRTELE.SPRTELE_STATUS_IND = 'A' OR SPRTELE.SPRTELE_STATUS_IND Is Null ) GROUP BY SPRTELE.SPRTELE_PIDM ) mSPRTELE ON mSPRTELE.SPRTELE_PIDM = SPRTELE.SPRTELE_PIDM AND mSPRTELE.SPRTELE_SEQNO = SPRTELE.SPRTELE_SEQNO

--) This is not a valid subquery --xSPRTELE --ON xSPRTELE.SPRTELE_PIDM = SFRSTCR_PIDM --AND xSPRTELE.SPRTELE_TELE_CODE IN ('CE', 'AA') --AND (xSPRTELE.SPRTELE_STATUS_IND = 'A' OR xSPRTELE.SPRTELE_STATUS_IND Is Null)

/r/SQL Thread
Previous
Next

Recently removed from /r/SQL

Cheat sheet for quotes usage? Trying to pivot data: need to derive missing rows and show latest values I finally landed a data engineer job Altering the type of a column from a table that was imported as a CSV file, but it's causing all the values to reset to blanks. What is going on here? Why uppercase is preferred in writing SQL queries? SQL Project: Don't know where to start I am SO confused on the concept of "JOINS" (Inner, Outer, Right, Left, etc) [2008R2] Could use some help formulating an approach for a complex problem involving time zones, daylight savings time, and lots of dateadds. Pivot table (or other solution) with Inner join [MsSQL] Non-ascii character removal [MySQL] Function Formatting With While and If Statements

More Random Comments

This is ridiculous Memorial dedicated to Savita Halappanavar, an Indian woman living in Ireland, who died of sepsis after being denied an abortion on the grounds that the fetus she was miscarrying still had a detectable heartbeat. Born color blind, decided to make color picking a little simple for my game. [serious]What's the saddest thing that ever happened to you? Dr. Feldman's 1st [Co-Authored] Article On MBI [Serious] When was the first time you had experienced racism? Mindfulness, which revolves around focusing on the present and accepting negative thoughts without judgment, is associated with reduced levels of procrastination. This suggests that developing mindfulness could help procrastinators cope with their procrastination. Game of Thrones fans who want a redo Those of you who have been in mental hospitals,workers OR patients, What was the most traumatic experience for you while in there? Feel Free to Correct me or Add anything The spinoff movie we all want to see Why do we have an Electoral College decide the fate of our country instead of the people who numerically pay the larger price for what happens? SJW's Who Are Actually Fans New Banable Offences on Owen's Livechat [please read] Help a guy get over a breakup?