• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 937
  • Last Modified:

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

0
portlight
Asked:
portlight
1 Solution
 
snoyes_jwCommented:
You can't set a variable to contain a row. Change the SELECT * to select only a single column, like SELECT id.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now