Solved

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

Posted on 2013-12-28
4
284 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
  • 2
  • 2
4 Comments
 
LVL 41

Expert Comment

by:graye
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now