Solved

Variables are not passing the correct or any data into query

Posted on 2013-12-19
1
278 Views
Last Modified: 2013-12-20
The date variables are not passing correctly in the store procedure.
This portion returns a null
(CONVERT_TZ( (FROM_UNIXTIME(@fromDate/1000)),''UTC'', @timeZone) )AS startingDate,
(CONVERT_TZ( (FROM_UNIXTIME(@toDate/1000)),''UTC'', @timeZone) ) AS endingDate,

These dates are show null

l1.started BETWEEN @startDate AND @endDate

What I am I missing that the values are not passing into the query
    CREATE PROCEDURE reportFreeCoolingTrackerTest (
            IN fromDate varchar (50),
            IN toDate varchar (50),
            IN timeZone varchar (50))
    
        BEGIN
                DECLARE startDate varchar (50);
                DECLARE endDate varchar (50);
                DECLARE mylogID Int;
    
                 SET startDate = FROM_UNIXTIME(fromDate/1000);
                 SET endDate = FROM_UNIXTIME(toDate/1000);
                 SET mylogID = (SELECT logID FROM logs l WHERE l.details LIKE 'FCT%');
                 SET @tbl = CONCAT('log',mylogID);
    
     set @q1 = CONCAT('SELECT 
        i.details,l1.item31985,l1.item31987,
        ((l1.item31985 - l1.item31987)*(time_to_sec(timediff(t2.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(t2.completed, l1.completed)) / 3600)) AS costT,
          time_to_sec(timediff(t2.completed, l1.completed)) / 3600 AS coolingHours,
          time_to_sec(timediff(@endDate, @startDate)) / 3600 AS totalTimeRange,
        (CONVERT_TZ( (FROM_UNIXTIME(@fromDate/1000)),''UTC'', @timeZone) )AS startingDate, 
        (CONVERT_TZ( (FROM_UNIXTIME(@toDate/1000)),''UTC'', @timeZone) ) AS endingDate,DATABASE() AS databaseName,
         CASE
            when l1.activityId = t2.activityId THEN 1
            ELSE 0
        END AS errorCheck, t2.completed AS errorDay,a.scheduleType,@lgName,@mylogID  
    
    
      FROM logs l 
          INNER JOIN groups g ON g.groupId = l.groupId
          LEFT JOIN groups g1 ON g.parentId = g1.groupId
          LEFT JOIN groups g2 ON g1.parentId = g2.groupId
          LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
          INNER JOIN activities a ON l.logId = a.logId 
          INNER JOIN  ',  @tbl,'  l1 ON a.activityId = l1.activityId ');
    
       set @q2 = CONCAT(@q1, 'INNER JOIN ', @tbl);
       
       SET @q3 = CONCAT(@q2, ' t2 ON t2.recordId = l1.recordid + 1
          INNER JOIN items i ON l.logId = i.logId AND i.name LIKE ''%KW%''
          INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE ''%KW%''
         WHERE i.itemID = "31985"  AND  l1.activityId = 1257
          AND l1.started
            BETWEEN @startDate
                AND @endDate
         ORDER BY l1.recordId,l1.started');   
        
        PREPARE stmt FROM @q3;
               
         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 8

Accepted Solution

by:
Surrano earned 500 total points
ID: 39731233
For me it seems you are missing some single quotes.  Try this: (hopefully I didn't botch)

    CREATE PROCEDURE reportFreeCoolingTrackerTest (
            IN fromDate varchar (50),
            IN toDate varchar (50),
            IN timeZone varchar (50))
    
        BEGIN
                DECLARE startDate varchar (50);
                DECLARE endDate varchar (50);
                DECLARE mylogID Int;
    
                 SET startDate = FROM_UNIXTIME(fromDate/1000);
                 SET endDate = FROM_UNIXTIME(toDate/1000);
                 SET mylogID = (SELECT logID FROM logs l WHERE l.details LIKE 'FCT%');
                 SET @tbl = CONCAT('log',mylogID);
    
     set @q1 = CONCAT('SELECT 
        i.details,l1.item31985,l1.item31987,
        ((l1.item31985 - l1.item31987)*(time_to_sec(timediff(t2.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(t2.completed, l1.completed)) / 3600)) AS costT,
          time_to_sec(timediff(t2.completed, l1.completed)) / 3600 AS coolingHours,
          time_to_sec(timediff(', @endDate, ',', @startDate,')) / 3600 AS totalTimeRange,
        (CONVERT_TZ( (FROM_UNIXTIME(',@fromDate,'/1000)),''UTC'', ',@timeZone,') )AS startingDate, 
        (CONVERT_TZ( (FROM_UNIXTIME(',@toDate,'/1000)),''UTC'', ',@timeZone,') ) AS endingDate,DATABASE() AS databaseName,
         CASE
            when l1.activityId = t2.activityId THEN 1
            ELSE 0
        END AS errorCheck, t2.completed AS errorDay,a.scheduleType,',@lgName,',',@mylogID,'  
    
    
      FROM logs l 
          INNER JOIN groups g ON g.groupId = l.groupId
          LEFT JOIN groups g1 ON g.parentId = g1.groupId
          LEFT JOIN groups g2 ON g1.parentId = g2.groupId
          LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
          INNER JOIN activities a ON l.logId = a.logId 
          INNER JOIN  ',  @tbl,'  l1 ON a.activityId = l1.activityId ');
    
       set @q2 = CONCAT(@q1, 'INNER JOIN ', @tbl);
       
       SET @q3 = CONCAT(@q2, ' t2 ON t2.recordId = l1.recordid + 1
          INNER JOIN items i ON l.logId = i.logId AND i.name LIKE ''%KW%''
          INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE ''%KW%''
         WHERE i.itemID = "31985"  AND  l1.activityId = 1257
          AND l1.started
            BETWEEN ',@startDate,'
                AND ',@endDate,'
         ORDER BY l1.recordId,l1.started');   
        
        PREPARE stmt FROM @q3;
               
         EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
        END //
    
    DELIMITER ;

Open in new window


PS. vim is your highlighting friend: www.vim.org
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 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