Looking for whether a row exists in a subquery

Question

Spoiler alert: I am fairly new to Oracle.
I have four tables: enrollments, courses/sections, standards, and grades.

We are running Honor Roll. I have queries on the first three tables that add various constraints needed to meet honor roll requirements. Then we look at the grades table. If they have a valid enrollment, in a valid course, meeting valid standards, then count up their scores. If their score qty meets thresholds, then they get Honors.

This code is not optimized, and likely can be done in a far better/more compact way I'm sure -- however, it only gets run a few times a year, so I'm willing to trade off optimization in order to increase human readability, so that I can continue to learn the fundamentals. So far I have:

WITH validCC (SELECT CC.ID AS CCID,
    CC.STUDENTID AS STUDENTID, 
    CC.SECTIONID AS SECTIONID, 
    CC.TERMID AS TERMID, 
    STUDENTS.DCID AS STUDENTSDCID
FROM CC
    INNER JOIN STUDENTS ON CC.STUDENTID = STUDENTS.ID
WHERE TERMID in (2700,2701)
    AND CC.SCHOOLID = 406;
),  --end validCC

validCrsSect (SELECT SECTIONS.ID AS SECTIONID,
    SECTIONS.DCID AS SECTIONSDCID,
    SECTIONS.EXCLUDEFROMHONORROLL AS SECTHR,
    COURSES.COURSE_NUMBER AS COURSE_NUMBER,
    COURSES.COURSE_NAME AS COURSE_NAME,
    COURSES.EXCLUDEFROMHONORROLL AS CRSHR
FROM SECTIONS
    INNER JOIN COURSES ON SECTIONS.COURSE_NUMBER = COURSES.COURSE_NUMBER AND SECTIONS.SCHOOLID = COURSES.SCHOOLID
WHERE SECTIONS.TERMID IN (2700,2701)
    AND SECTIONS.SCHOOLID = 406
    AND SECTIONS.EXCLUDEFROMHONORROLL = 0
    AND COURSES.EXCLUDEFROMHONORROLL = 0
), --end validCrsSect

validStandard (SELECT STANDARDID,
    IDENTIFIER,
    TRANSIENTCOURSELIST
FROM STANDARD
WHERE isActive = 1
    AND YEARID = 27
    AND ( instr (STANDARD.identifier, 'MHS.TS', 1 ,1) > 0     --Is a valid standard for this criteria:  MHS TS
        or STANDARD.identifier = 'MHTC.TS.2'                  --or MHTC TS
        or STANDARD.identifier = 'MHTC.TS.4'  )
), --end validStandard

--sgsWithChecks ( 
SELECT sgs.STANDARDGRADESECTIONID AS SGSID,
    sgs.STUDENTSDCID as STUDENTSDCID,
    sgs.STANDARDID AS STANDARDID,
    sgs.STORECODE AS STORECODE,
    sgs.SECTIONSDCID AS SECTIONSDCID,
    sgs.YEARID AS YEARID,
    sgs.STANDARDGRADE AS STANDARDGRADE,
    (select count(CCID) from validCC INNER JOIN STANDARDGRADESECTION sgs ON sgs.STUDENTSDCID = validCC.STUDENTSDCID and sgs.SECTIONSDCID = validCC.SECTIONID) as CC_OK,
    (select count(SECTIONID) from validCrsSection  INNER JOIN STANDARDGRADESECTION sgs ON  sgs.SECTIONSDCID = validCrsSect.SECTIONSDCID) AS CRS_OK,
    (select count(STANDARDID) from validStandard  INNER JOIN STANDARDGRADESECTION sgs ON  sgs.STANDARDID = validStandard.STANDARDID) AS STD_OK
FROM STANDARDGRADESECTION sgs

The purpose of putting the 'OK' columns in the vGrades table is because the final SELECT (not included) goes through and counts up the instances of certain scores filtering by the checks.

Frustratingly, there are two IDs in both the students table and the sections table (and it's not the same data). So when I go to link everything, some tables use ID as the FK, others use DCID as the FK; and I have to pull in an extra table to make that conversion. Makes the joins more fun that way I guess.

Each individual query works on its own, but I can't get the final select count() to work to pull their data. I tried embedding the initial queries as subqueries, but I couldn't pass the studentid into them, and it would run that query for each student, instead of once at the beginning.

My current error is:

Error starting at line : 13 in command -
    SECTIONS.DCID AS SECTIONSDCID,
Error report -
Unknown Command

However before it was saying unknown table and referencing the last line of the join statement. All the table names are valid. Thoughts?

Answer 1

I replaced the INNER JOIN with a simple WHERE condition. This seems to work.

(SELECT COUNT (CCID) FROM validCC WHERE sgs.STUDENTSDCID = validCC.STUDENTSDCID and sgs.SECTIONSDCID = validCC.SECTIONID) as CC_OK,
(SELECT COUNT (SECTIONID) FROM validCrsSect WHERE sgs.SECTIONSDCID = validCrsSect.SECTIONSDCID) AS CRS_OK,
(SELECT COUNT (STANDARDID) FROM validStandard WHERE sgs.STANDARDID = validStandard.STANDARDID) AS STD_OK

Answer 2

I removed the stray comma at the end of validStandard and replaced from validCrsSection with from validCrsSect (assuming it was meant to refer to that WITH clause and there isn't another validCrsSection table). I am also guessing that the counts are meant to be keyed to the current sgs row and not counts of the whole table. I make it this:

with validcc as
     ( select cc.id as ccid
            , cc.studentid
            , cc.sectionid
            , cc.termid
            , st.dcid as studentsdcid
       from   cc
              join students st on st.id = cc.studentid
       where  cc.termid in (2700, 2701)
       and    cc.schoolid = 406
     )
   , validcrssect as
     ( select s.id as sectionid
            , s.dcid as sectionsdcid
            , s.excludefromhonorroll as secthr
            , c.course_number
            , c.course_name
            , c.excludefromhonorroll  as crshr
       from   sections s
              join courses c
                   on  c.course_number = s.course_number
                   and c.schoolid = s.schoolid
       where  s.termid in (2700, 2701)
       and    s.schoolid = 406
       and    s.excludefromhonorroll = 0
       and    c.excludefromhonorroll = 0
     )
   , validstandard as
      ( select standardid
             , identifier
             , transientcourselist
        from   standard
        where  isactive = 1
        and    yearid = 27
        and    (   instr(standard.identifier, 'MHS.TS', 1, 1) > 0
                or standard.identifier in ('MHTC.TS.2','MHTC.TS.4') )
     )
select sgs.standardgradesectionid as sgsid
     , sgs.studentsdcid
     , sgs.standardid
     , sgs.storecode
     , sgs.sectionsdcid
     , sgs.yearid
     , sgs.standardgrade
     , ( select count(*) from validcc
         where  validcc.studentsdcid = sgs.studentsdcid
         and    validcc.sectionid = sgs.sectionsdcid ) as cc_ok
     , ( select count(*) from validcrssect
         where  validcrssect.sectionsdcid = sgs.sectionsdcid ) as crs_ok
     , ( select count(*) from validstandard
         where  validstandard.standardid = sgs.standardid ) as std_ok
from   standardgradesection sgs;

This works with the six table definitions reverse-engineered as:

create table students
( id integer not null
, dcid integer );

create table cc
( id integer
, studentid integer
, sectionid integer
, termid integer
, schoolid integer );

create table courses
( course_number integer
, course_name varchar2(30)
, excludefromhonorroll integer
, schoolid integer );

create table sections
( id integer not null
, dcid integer
, excludefromhonorroll integer
, termid integer
, schoolid integer
, course_number integer );

create table standard
( standardid integer
, identifier varchar2(20)
, transientcourselist varchar2(50)
, isactive integer
, yearid integer );

create table standardgradesection
( standardgradesectionid integer
, studentsdcid integer
, standardid integer
, storecode integer
, sectionsdcid integer
, yearid integer
, standardgrade integer );