Need help with a tough SQL query

I have a query that I am currently getting the ORA-01427: single-row subquery returns more than one row error on. I understand the error, and what is causing it, but cant figure out a way to fix it. I would also like to do this in just one query. Here is what I currently have:

SELECT   v1.internal_code,
       (SELECT   terms_id
          FROM   terms
         WHERE   term_start_date =
                    (SELECT   MIN (term_start_date)
                       FROM   terms
                      WHERE   terms_id LIKE '%SU'
                              AND term_start_date >
                                    (SELECT   term_start_date
                                       FROM      terms
                                              JOIN
                                                 vals
                                              ON terms_id =
                                                    internal_code
                                      WHERE   internal_code =
                                                 v1.internal_code
                                              AND valcode_id =
                                                    'TERMS')))
          AS mmTerm
FROM      terms
       JOIN
          vals v1
       ON terms_id = internal_code
  WHERE   internal_code LIKE '%SP' AND valcode_id = 'WEB.SEARCH.TERMS'
 ORDER BY   mmTerm ASC

And the relevant part of the vals table would be this:

internal_code      valcode_id
-------------      ----------
   2003SP            TERMS
   2004SP            TERMS
开发者_运维知识库   2005SP            TERMS

Okay, so the big subquery is attempting to get any terms that end with 'SU' where the term_start_date is greater than the term_start_date of the terms in the vals table. So the desired result is:

v1.internal_code      mmTerm
----------------      ------
    2003SP            2003SU
    2004SP            2004SU
    2005SP            2005SU

I know this is tough to understand, so please ask questions if there are any. Also, any suggestions are gladly accepted. Thanks!

EDIT: I figured it out. Just had to do some reworking, thanks for the suggestions. I will post it if anyone is interested in seeing the final query

The problem is in subquery that is used here: AND term_start_date > ...

It must return a single value. The best solution I think is to use MAX():

AND term_start_date >
    (SELECT MAX(term_start_date) -- USE MAX HERE
    FROM terms
    JOIN vals
    ON terms_id = internal_code
    WHERE internal_code = v1.internal_code
    AND valcode_id = 'TERMS')

Using MAX() means term_start_date must be greater than all term_start_date found in the subquery).

If you need it to be greater than any, use MIN() instead.

EDITED I swear you have edited your original query! Anyway, try using an aggregate on the outer select, like this:

SELECT   v1.internal_code,
   (SELECT   MAX(terms_id)
      FROM   terms
      ...

That's quite hard to read - you need to add aliases to all the table references. The error can be gotten around if you don't care about which row the subquery returns - simply return MIN(terms_id) or MAX(terms_id) or whatever to only bring back 1 row. But you may need to think about your data more deeply - does it really matter which term the subquery returns?

SELECT   v1.internal_code,
       (SELECT   MIN(terms_id)
          FROM   terms
         WHERE   term_start_date =
                    (SELECT   MIN (term_start_date)
                       FROM   terms
                      WHERE   terms_id LIKE '%SU'
                              AND term_start_date >
                                    (SELECT   term_start_date
                                       FROM      terms
                                              JOIN
                                                 vals
                                              ON terms_id =
                                                    internal_code
                                      WHERE   internal_code =
                                                 v1.internal_code
                                              AND valcode_id =
                                                    'TERMS')))
          AS mmTerm
FROM      terms
       JOIN
          vals v1
       ON terms_id = internal_code
  WHERE   internal_code LIKE '%SP' AND valcode_id = 'WEB.SEARCH.TERMS'
 ORDER BY   mmTerm ASC

Are you expecting only one record to match the subquery conditions? Than you should add a TOP 1 to your subquery:

...
(SELECT TOP 1 terms_id ...