What is causing this errand in the stored procedure. (Operand should contain 1 column(s))

I am trying to set myItemId so that I can use it in the concat query. Everything works fine until I add this row

  SET myItemID = (SELECT * FROM items i  WHERE i.name LIKE '%KW PRE FREE COOLING%');

Open in new window

It then gives me an error of Operand should contain 1 column(s)

Here is the query that I am working with

CREATE PROCEDURE reportFreeCoolingTempTable (
  IN fromDate VARCHAR (50),
  IN toDate   VARCHAR (50),
  IN timeZone VARCHAR (50)
)
BEGIN
  DECLARE startDate VARCHAR (50);
  DECLARE endDate   VARCHAR (50);
  DECLARE mylogID   INT;
  DECLARE myItemID  int;

  SET startDate = FROM_UNIXTIME(fromDate/1000);
  SET endDate   = FROM_UNIXTIME(toDate/1000);
  SET mylogID   = (SELECT logID FROM logs WHERE details LIKE 'FCT%' LIMIT 1);
  SET myItemID = (SELECT * FROM items i  WHERE i.name LIKE '%KW PRE FREE COOLING%');

  SET @sql = NULL;
    
  SET @sql = NULL;
  SET @sql = CONCAT(
  'SELECT @row:=@row+1 as rownum,
       a.logid ,   
       L1.recordId,
       L2.recordId as next_recordId,
       L1.completed,
       L2.completed as next_completed,
       L1.activityId,
       L2.activityId as next_activityId,
       IF(L1.activityId = L2.activityId,1,NULL) as isError,                           
       TIME_TO_SEC(TIMEDIFF(L2.completed, L1.completed)) / 3600 AS coolingHours,
       ((L1.item31985 - L1.item31987) * (time_to_sec(timediff(L2.completed, L1.completed)))) / 3600  AS kwDifference,
     ((L1.item31985 - L1.item31987) * (substr(l.details, instr(l.details , '':'' ) +1))) AS cost,
    ( (((L1.item31985 - L1.item31987) * (substr(l.details, instr(l.details , '':'' ) +1)))
    *(time_to_sec(timediff(L2.completed, L1.completed)) / 3600))) AS costT,
     time_to_sec(timediff(''', endDate, ''', ''', startDate, ''')) / 3600 AS totalTimeRange,
    CONVERT_TZ(''', startDate, ''', ''UTC'', ''', timeZone, ''') AS startingDate, 
    CONVERT_TZ(''', endDate, ''', ''UTC'', ''', timeZone, ''') AS endingDate,
    DATABASE() AS databaseName

FROM
    (SELECT @row:=0)R,
    (SELECT T1.completed,
       (SELECT MIN(completed)
         FROM log1644
         WHERE completed > T1.completed) AS next_completed
      FROM log',mylogID, ' T1
      ORDER BY T1.completed
     )TimeOrder
        LEFT JOIN log', mylogID, ' L1 ON (L1.completed = TimeOrder.completed)
        LEFT JOIN log', mylogID, ' L2 ON (L2.completed = TimeOrder.next_completed)
        LEFT JOIN activities a ON L1.activityId = a.activityId
        LEFT JOIN logs l ON a.logId = l.logId
        Left Join items i ON l.logId = i.logId AND i.name LIKE ''%KW%''
    WHERE i.itemID = 31985  
        AND L1.completed BETWEEN ''', startDate, ''' AND ''', endDate, '''
ORDER BY L1.completed');
    
        
        
 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
END //


DELIMITER ;

Open in new window

portlightAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

snoyes_jwCommented:
You can't set a variable to contain a row. Change the SELECT * to select only a single column, like SELECT id.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.