Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Variables are not passing the correct or any data into query

Posted on 2013-12-19
1
Medium Priority
?
281 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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

971 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