How to fix ORA-01427 Single-Row Subquery Returns More Than One Row

Question

Whenever I add the indented section of code listed below (LastTerm) the error ORA-01427 Single-Row Subquery Returns More Than One Row is returned by SQL Tools. If I removed this from section of code from my list, it runs and correctly pulls the data that I ask for from the tables. What do I need to change or fix to get this to run with that LastTerm section in the code instead of receiving that error? I'm not sure what I am doing wrong and I am not sure why the other two, Major and Standing, do run.

Any help with this would be greatly appreciated.

SELECT DISTINCT (c.sfrstcr_pidm)PIDM,
substr(frkiden.f_get_spriden_id(c.sfrstcr_pidm),1,9) ID,
substr(frkiden.f_get_spriden_first_name(c.sfrstcr_pidm),1,15) FIRST,
substr(frkiden.f_get_spriden_last_name(c.sfrstcr_pidm),1,60) LAST,
sovclas_clas_code,shrlgpa_gpa,spbpers_ethn_code,spbpers_sex,

  (select x. sgbstdn_majr_code_1
  from sgbstdn x where x.sgbstdn_pidm = c.sfrstcr_pidm
  and x.sgbstdn_term_code_eff =
  (select max(y. sgbstdn_term_code_eff) from sgbstdn y
  where x. sgbstdn_pidm = y. sgbstdn_pidm
  and y. sgbstdn_majr_code_1 is not null)) Major,

  (select x.shrttrm_astd_code_end_of_term
  from shrttrm x where x.shrttrm_pidm = c.sfrstcr_pidm
  and x.shrttrm_term_code =
  (select max(y.shrttrm_term_code) from shrttrm y
  where x.shrttrm_pidm = y.shrttrm_pidm
  and y.shrttrm_astd_code_end_of_term is not null)) Standing,

      (select x.sfrstcr_term_code
      from sfrstcr x where x.sfrstcr_pidm = c.sfrstcr_pidm
      and x.sfrstcr_term_code =
      (select max(y.sfrstcr_term_code) from sfrstcr y
      where x.sfrstcr_pidm = y.sfrstcr_pidm
      and y.sfrstcr_term_code IS NOT NULL )) LastTerm

FROM
sovclas,spbpers,shrlgpa,sfrstcr c

LEFT JOIN goremal
ON c.sfrstcr_pidm = goremal_pidm
AND goremal_emal_code = 'UNIV'
AND goremal_status_ind != 'I'

WHERE
c.sfrstcr_pidm = shrlgpa_pidm
AND c.sfrstcr_term_code = sovclas_term_code
AND c.sfrstcr_pidm = sovclas_pidm
AND c.sfrstcr_pidm = spbpers_pidm
AND shrlgpa_levl_code = 'UG'
AND shrlgpa_gpa_type_ind = 'O'
AND c.sfrstcr_term_code = sovclas_term_code
AND c.sfrstcr_term_code = '201510'
AND c.sfrstcr_rsts_code IN ('RE','RW','RL')
AND sovclas_clas_code NOT IN ('GR','ES')
AND c.sfrstcr_pidm IN

(SELECT DISTINCT(a.shrttrm_pidm)
FROM shrttrm a
WHERE a.shrttrm_astd_code_end_of_term IN ('S1','S2','S3','S4')

  AND a.shrttrm_term_code =

  (SELECT Max(b.shrttrm_term_code)
  FROM shrttrm b
  WHERE b.shrttrm_pidm = a.shrttrm_pidm AND
        b.shrttrm_term_code < 201510 ))  -- Change the code to the latest term without standing

        AND c.sfrstcr_pidm IN

        (SELECT DISTINCT(a.sgbstdn_pidm)
        FROM sgbstdn a
        WHERE a.sgbstdn_levl_code != 'GR'
        AND a.sgbstdn_styp_code NOT IN ('L')
        AND a.sgbstdn_term_code_eff =
        (SELECT Max(b.sgbstdn_term_code_eff)
        FROM sgbstdn b
        WHERE b.sgbstdn_pidm = a.sgbstdn_pidm))

        AND c.sfrstcr_pidm IN

        (SELECT DISTINCT(a.sfrstcr_pidm)
        FROM sfrstcr a
        WHERE a.sfrstcr_rsts_code IS NOT NULL
        AND a.sfrstcr_term_code =
        (SELECT Max(b.sfrstcr_term_code)
        FROM sfrstcr b
        WHERE b.sfrstcr_pidm = a.sfrstcr_pidm AND
              b.sfrstcr_term_code < '201510'))

Answer 1

  (select max(x.sfrstcr_term_code)
  from sfrstcr x where x.sfrstcr_pidm = c.sfrstcr_pidm
  and x.sfrstcr_term_code =
  (select max(y.sfrstcr_term_code) from sfrstcr y
  where x.sfrstcr_pidm = y.sfrstcr_pidm
  and y.sfrstcr_term_code IS NOT NULL )) LastTerm

Answer 2

Try adding (rownum = 1) like this:

(select x.shrttrm_astd_code_end_of_term
  from shrttrm x where x.shrttrm_pidm = c.sfrstcr_pidm
  and x.shrttrm_term_code =
  (select max(y.shrttrm_term_code) from shrttrm y
  where x.shrttrm_pidm = y.shrttrm_pidm
  and y.shrttrm_astd_code_end_of_term is not null
  and rownum = 1)) Standing