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

Variables are not passing the correct or any data into query

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
portlight
Asked:
portlight
1 Solution
 
SurranoSystem EngineerCommented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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