CODEWITHSUNDEEP
sqlsql-serverrow
Ben S
Ben S

Reputation: 1

SQL Query, error returning more than one row

I input the following:

SELECT
    APPL_ACAD_PROGRAM,
    APPL_START_TERM,
    APPL_APPLICANT,
    STC_TERM,
    STC_PERSON_ID
FROM 
    ODS_APPLICATIONS
JOIN 
    SPT_STUDENT_ACAD_CRED ON STC_PERSON_ID = APPL_APPLICANT
WHERE
    APPL_ACAD_PROGRAM = (
        SELECT 
            (APPL_ACAD_PROGRAM) 
        FROM ODS_APPLICATIONS
        WHERE APPL_START_TERM LIKE STC_TERM + '%'
        AND APPL_APPLICANT = STC_PERSON_ID
)

I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I understand that this is related to my nested query pulling more than one row which SQL seems to disagree with, any ideas to circumvent this issue?

Upvotes: 0

Views: 5111

Answers (3)

MyStackFlowethOver
MyStackFlowethOver

Reputation: 197

If you're only wanting the first result for the sub query, it should read select top 1 not select.

If you're wanting the sub query to return more than 1 result, and your main query to sift through more than one result, you can replace = to in , and the sub query won't need to be changed.

Upvotes: 1

Jason A. Long
Jason A. Long

Reputation: 4442

Try rewriting it as an EXISTS...

FROM 
    ODS_APPLICATIONS oa
    JOIN SPT_STUDENT_ACAD_CRED ssad
        ON oa.STC_PERSON_ID = ssad.APPL_APPLICANT
WHERE
    EXISTS (
            SELECT 1 
            FROM 
                ODS_APPLICATIONS oa2 
            WHERE 
                oa.APPL_ACAD_PROGRAM = oa2.APPL_ACAD_PROGRAM
                AND APPL_START_TERM LIKE STC_TERM + '%'
                AND APPL_APPLICANT = STC_PERSON_ID
                );

Upvotes: 0

Jacobm001
Jacobm001

Reputation: 4539

The = operator only allows you to compare against a single value. Your query is returning more than one value. You can either change your query to only return one value, or change your operator.

The in operator will return true if the value matches any of the returned values.

You could also modify the = operator with the all operator. If you select with = all the expression return true if the value matches all of the values returned.

Which solution you use is entirely dependent on what you want to achieve. My guess is you want in.

SELECT
APPL_ACAD_PROGRAM,
APPL_START_TERM,
APPL_APPLICANT,
STC_TERM,
STC_PERSON_ID

FROM ODS_APPLICATIONS
JOIN SPT_STUDENT_ACAD_CRED ON STC_PERSON_ID = APPL_APPLICANT

WHERE
APPL_ACAD_PROGRAM in (SELECT (APPL_ACAD_PROGRAM) FROM ODS_APPLICATIONS
WHERE APPL_START_TERM LIKE STC_TERM + '%'
AND APPL_APPLICANT = STC_PERSON_ID)

Upvotes: 1

Related Questions