Solved

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

Posted on 2013-12-26
1
829 Views
Last Modified: 2013-12-26
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
Comment
Question by:portlight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 500 total points
ID: 39740605
You can't set a variable to contain a row. Change the SELECT * to select only a single column, like SELECT id.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question