MYSQL - Operand should contain 1 column(s) - Nested Case statement [closed]

Question

I have written the below query with nested case statements. The syntax checks out but when I run it I receive error code 1241: Operand should contain only 1 column(s). I have attempted to remove the parenthesis from the SELECT statements but it does not help and indicates there is a syntax error.

This query is intended to select a data subset based on current time. The first case selects data from today's first shift. The second nested case determines if the current time the night portion of second shift (Between now and midnight 2nd shift) or the early morning portion of second shift between midnight and 4:30. If it is after midnight then date from today and today-1 is selected. Any ideas?

SELECT CASE WHEN current_time() BETWEEN CAST('6:00:00' AS TIME) AND CAST('16:30:00' AS TIME)
    THEN
        (SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp) 
            FROM workers_totals
            WHERE time_stamp >= curdate())
    ELSE
        CASE WHEN (current_time() BETWEEN CAST('16:30:00' AS TIME) AND CAST('23:59:59' AS TIME))
            THEN 
                 (SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
                 FROM workers_totals
                 WHERE time_stamp >= CAST(CONCAT(curdate(),' 16:30:00') AS DATETIME)  and CAST(CONCAT(curdate(),' 23:59:59') AS DATETIME))
            ELSE
                 (SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp) 
                 FROM workers_totals
                 WHERE (time_stamp >= CAST(CONCAT(curdate(),' 00:00:00') AS DATETIME)  and CAST(CONCAT(curdate(),' 04:59:59') AS DATETIME))
                 OR (time_stamp >= CAST(CONCAT(curdate()-1,' 16:30:00') AS DATETIME)  and CAST(CONCAT(curdate()-1,' 23:59:59') AS DATETIME)))
            END
    END

Answer 1

CASE in MySQL can either be a statement, or an expression.

Since your CASE statement is in a SELECT list, it's being treated as an expression. As an expression, it should return a single value. You're trying to return multiple columns (and, presumably multiple rows).

Presumably, you want to use CASE as a statement, without the SELECT in front, so that just one of your three SELECT statements is executed:

CASE WHEN current_time() BETWEEN CAST('6:00:00' AS TIME) AND CAST('16:30:00' AS TIME)
     THEN 
          SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp) 
            FROM workers_totals
           WHERE time_stamp >= curdate()
     WHEN (current_time() BETWEEN CAST('16:30:00' AS TIME) AND CAST('23:59:59' AS TIME)
        THEN
             SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
               FROM workers_totals
              WHERE time_stamp BETWEEN CAST(CONCAT(curdate(),' 16:30:00') AS DATETIME) and CAST(CONCAT(curdate(),' 23:59:59') AS DATETIME)
     ELSE
             SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp) 
               FROM workers_totals
              WHERE (time_stamp BETWEEN CAST(CONCAT(curdate(),' 00:00:00') AS DATETIME) and CAST(CONCAT(curdate(),' 04:59:59') AS DATETIME))
                 OR (time_stamp BETWEEN CAST(CONCAT(curdate()-1,' 16:30:00') AS DATETIME) and CAST(CONCAT(curdate()-1,' 23:59:59') AS DATETIME))
END CASE

Note that the CASE statement is terminated with END CASE instead of just END.

Also, I've condensed your code to use one CASE statement instead of two nested ones (you can have multiple WHEN branches with CASE), and changed your timestamp comparisons to check if the value is BETWEEN the two dates you supply, instead of timestamp >= dateX AND dateY.

You should double-check your time_stamp boundaries - for times between midnight and 6AM, you're cutting off anything with a timestamp later than today at 5AM.

Finally, note that this can only be used in a stored program.

I believe you could do this as a single SELECT statement, without needing a CASE at all:

SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp) 
  FROM workers_totals
 WHERE (    current_time() < CAST('6:00:00' AS TIME)
        AND time_stamp >= CAST(CONCAT(curdate()-1,' 16:30:00') AS DATETIME)
        AND time_stamp <=CAST(CONCAT(curdate(),' 04:59:59') AS DATETIME)
       )
    OR (    current_time() >= CAST('6:00:00' AS TIME)
        AND current_time() < CAST('16:30:00' AS TIME)
        AND time_stamp >= curdate()
       )
    OR (    current_time() >= CAST('16:30:00' AS TIME)
        AND time_stamp >= CAST(CONCAT(curdate(),' 16:30:00')
        AND time_stamp <= CAST(CONCAT(curdate(),' 23:59:59') AS DATETIME)
       )
;

I've tweaked your current_time() checks, to try to make sure that all times are covered, and fall uniquely into one range or another. You'll want to validate that this works as intended.

NOTE: All code is untested.