Solved

Variables are not passing the correct or any data into query

Posted on 2013-12-19
1
275 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

777 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