CODEWITHSUNDEEP
sqlsql-serverssrs-2012reportbuilder3.0
Alan Kael Ball
Alan Kael Ball

Reputation: 680

SQL Server / Report Builder subquery returns more than one row error

I'm struggling to figure out why a subquery in report builder is returning more than one row.

(
    SELECT 
    (
        CASE
            WHEN C.CourseCode IN ('50089079','50089080') THEN 'L2 Maths FS'
            WHEN C.CourseCode IN ('50089067','50089109') THEN 'L1 Maths FS'
            WHEN C.CourseCode IN ('50084987','50092959') THEN 'E3 Maths FS'
            WHEN C.CourseCode IN ('50084975','50091967') THEN 'E2 Maths FS'
            WHEN C.CourseCode IN ('50084963','50091724') THEN 'E1 Maths FS'
            WHEN C.CourseCode IN ('60146084') THEN 'GCSE Maths'
            Else 'NA'
        END
    )
    FROM
        Enrolment E
    INNER JOIN 
        Course C ON C.CourseID = E.CourseID
    WHERE 
        E.PMStudentID = vReports_Enrolment.PMStudentID
        AND C.CourseCode IN ('50089079', '50089080', '50089067', '50089109', '50084987', '50092959', '50084975', '50091967', '50084963', '50091724', '60146084')
        AND vReports_Enrolment.CompletionID = 1
)

This is the data for a specific learner where this error is popping up - I've highlighted where there would usually be 2 rows returned if not for the CompletionID being checked to see if it's '1':

CourseCode  CompletionID  
-------------------------
50044357    1 
50044369    1
50089079    0   
60146084    1
60187578    1
60148366    1

The expected behavior in this case is to return 'GCSE Maths' - am I doing something wrong?

Upvotes: 0

Views: 159

Answers (2)

Alan Schofield
Alan Schofield

Reputation: 21703

In some cases you have two rows or more rows.

Using TOP 1 will only choose the first which is no guarantee that it's the one you want, especially if your data is not as clean as you think.

It is safer to use SELECT DISTINCT ... . That way, if all returned rows are the same, just duplicates, then you will get the correct answer. If you still get an error then you need to investigate the sub-query results.

Upvotes: 1

Alan Kael Ball
Alan Kael Ball

Reputation: 680

Added:

(
    SELECT TOP 1
    (
        CASE

Ensure only one row is returned, which is the expected behavior.

Upvotes: 0

Related Questions