[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

IF statement is not formatted correctly and not sure how to fix

Posted on 2013-12-28
4
Medium Priority
?
296 Views
Last Modified: 2013-12-29
I am trying to set the startDate of the query to fiirst_completed based on a set of 'if true' conditions. I am getting an error that the syntax is wrong.
I am open to suggestions on how to do this.
I am including the entire query that I am working with as well as the piece separately that is giving me trouble.

specific part of query that is throwing an error
SELECT 
    IF (coolingHours is null AND rowNum = min(rowNum) THEN ''',startDate,''':= first_completed END IF, 
    rowNum,l.logId,l.completed,l.next_completed,l.first_completed,
    isError,charge,totalTimeRange,startingDate,endingDate,coolingHours,
    databaseName,i.name,l.itemId, kwDifference, cost, costT,l.details,timeInSeconds
  FROM tempTable3 l
   inner join items i ON l.logId = i.logId AND i.name LIKE ''%KW PRE%'' 
   WHERE l.itemId = ''',myItemId,'''
        AND completed BETWEEN ''', startDate, ''' AND ''', endDate, '''

Open in new window


full query

DECLARE startDate VARCHAR (50);
  DECLARE endDate   VARCHAR (50);
  DECLARE mylogID   INT;
  DECLARE myItemId varchar (50);
  DECLARE myItemId2 varchar (50);
  DECLARE xHours varchar (50);

  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 i.itemId FROM items i  WHERE i.name LIKE '%KW PRE%');
  SET myItemID2 = (SELECT i.itemId FROM items i  WHERE  i.name LIKE '%KW STA%');

  SET @sql = NULL;
  SET @sql = CONCAT(
 'CREATE TEMPORARY TABLE tempTable3 AS (SELECT
    @row := @row + 1 AS rownum,
    a.logId,
    L1.recordId,
    L2.recordId AS next_recordId,
    L1.completed,
    L2.completed AS next_completed,
    L3.completed AS first_completed,
    L1.activityId AS activityJoin,
    L2.activityId AS next_activityId,
    IF(L1.activityId = L2.activityId, 1, NULL) AS isError,
     CASE
        when a.name LIKE ''%Enable%'' THEN time_to_sec(timediff(L2.completed, L1.completed)) / 3600
        ELSE NULL
        END AS coolingHours,
     TO_SECONDS(L2.completed) - TO_SECONDS(L1.completed) AS timeInSeconds,
    ((SUBSTR(l.details, INSTR(l.details, '':'') + 1))) AS charge,
    time_to_sec(timediff(''', endDate, ''', ''', startDate, ''')) / 3600 AS totalTimeRange,
    CONVERT_TZ(''', startDate, ''', ''UTC'', ''US/Pacific'') AS startingDate, 
    CONVERT_TZ(''', endDate, ''', ''UTC'', ''US/Pacific'') AS endingDate,
    ((L1.item', myItemID, ' - L1.item', myItemID2, ') * 
        (TIME_TO_SEC(TIMEDIFF(L2.completed, L1.completed)))) / 3600 AS kwDifference,
    ((L1.item', myItemID, ' - L1.item', myItemID2, ') * (SUBSTR(l.details, INSTR(l.details, '':'') + 1))) AS cost,
    ((((L1.item', myItemID, ' - L1.item', myItemID2, ') * (SUBSTR(l.details, INSTR(l.details,'':'') + 1)))
    * (TIME_TO_SEC(TIMEDIFF(L2.completed, L1.completed)) / 3600))) AS costT,
    DATABASE() AS databaseName, i.itemId ,l.details
FROM
    (SELECT @row:=0)R,
    (SELECT T1.completed
         , (SELECT MIN(T2.completed) 
            FROM log',mylogID, ' T2
            WHERE T2.completed > T1.completed) AS next_completed
         , (SELECT MAX(T2.completed) 
            FROM log',mylogID, ' T2
            WHERE T2.completed < T1.completed) AS frst_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 log', mylogID, ' L3 ON (L3.completed = TimeOrder.frst_completed)
        LEFT JOIN activities a ON L1.activityId = a.activityId
        LEFT JOIN logs l ON a.logId = l.logId
        inner join items i ON l.logId = i.logId AND i.name LIKE ''%KW PRE%'' 
    )order by L1.completed');  

 SET @sqlTemp = NULL;
  SET @sqlTemp = CONCAT(
  'SELECT 
    IF (coolingHours is null AND rowNum = min(rowNum) THEN ''',startDate,''':= first_completed END IF, 
    rowNum,l.logId,l.completed,l.next_completed,l.first_completed,
    isError,charge,totalTimeRange,startingDate,endingDate,coolingHours,
    databaseName,i.name,l.itemId, kwDifference, cost, costT,l.details,timeInSeconds
  FROM tempTable3 l
   inner join items i ON l.logId = i.logId AND i.name LIKE ''%KW PRE%'' 
   WHERE l.itemId = ''',myItemId,'''
        AND completed BETWEEN ''', startDate, ''' AND ''', endDate, '''
 '); 

PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;

PREPARE stmtTemp FROM @sqlTemp;
 EXECUTE stmtTemp;
 DEALLOCATE PREPARE stmtTemp;

 DROP TEMPORARY TABLE tempTable3

Open in new window


sample of dataset

row Num      completed      next_completed      first_completed      cooling Hours
10      11/27/2013 1:05:45 AM      11/29/2013 11:18:34 PM      11/25/2013 11:21:15 PM      (null)
11      11/29/2013 11:18:34 PM      11/30/2013 7:37:45 PM      11/27/2013 1:05:45 AM      20.3197
12      11/30/2013 7:37:45 PM      11/30/2013 11:36:57 PM      11/29/2013 11:18:34 PM      (null)
13      11/30/2013 11:36:57 PM      12/1/2013 6:52:19 PM      11/30/2013 7:37:45 PM      19.2561
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
  • 2
  • 2
4 Comments
 
LVL 41

Expert Comment

by:graye
ID: 39744070
OK, the problem is that you can't assign a value to the startDate variable while inside the CONCAT() function.   It needs to be in a separate line all by itself.

So, that means you'll have to move that IF statement up a few lines so that it's before the CONCAT().  This statement would look very similar to the other variables that you set via queries (like how to set mylogID at the top).

Set startDate = SELECT IF (coolingHours is null AND rowNum = min(rowNum) THEN first_completed END IF FROM tempTable3..... (obviously not complete statement)
0
 

Author Comment

by:portlight
ID: 39744093
I change it to this

  Set startDate = (SELECT IF (coolingHours is null AND rowNum = min(rowNum))
                            THEN first_completed END IF FROM tempTable3);
Set startDate = (SELECT (IF coolingHours is null AND rowNum = min(rowNum) 
                            THEN first_completed END IF) FROM tempTable3);
  SET @sqlTemp = NULL;
  SET @sqlTemp = CONCAT(
  'SELECT 
    rowNum,l.logId,l.completed,l.next_completed,l.first_completed,
    isError,charge,totalTimeRange,startingDate,endingDate,coolingHours,
    databaseName,i.name,l.itemId, kwDifference, cost, costT,l.details,timeInSeconds
  FROM tempTable3 l
   inner join items i ON l.logId = i.logId AND i.name LIKE ''%KW PRE%'' 
   WHERE l.itemId = ''',myItemId,'''
        AND completed BETWEEN ''', startDate, ''' AND ''', endDate, '''
 
 '); 

Open in new window


which is giving me format errors in the set statement - however my concern is this - table three grabs ALL of the rows where as the actual dataset needed (@sqlTemp) only returns the rows between startDate and endDate - it is this set that I actually need to test against and then set startDate to first_completed if true....
0
 
LVL 41

Accepted Solution

by:
graye earned 1500 total points
ID: 39744760
You're on the right track.   And yes, you might have to include a bit more of the "main" query in the query for the set statement.  I can't tell if you need all of the logic minus the between statement or not.... but that would be a good place to start.

And yes, you will be executing the query twice.... once to get the startDate value, and then again to use the startDate in the "main" query.

However, I'm concerned about the "min(rowNum)" part of the statement.   There isn't a Group By to match this part.
0
 

Author Comment

by:portlight
ID: 39744835
This worked

SET @startDate = (SELECT MIN(completed) FROM tempTable3 WHERE coolingHours is NULL AND 
        completed BETWEEN startDate AND endDate);
    SET @startDate2 = (SELECT MAX(completed) FROM tempTable3 WHERE completed < @startDate);
    SET startDate = @startDate2;

Open in new window

0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

649 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