Solved

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

Posted on 2013-12-28
4
288 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
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 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Error 3 43
PHP connection to remote AWS MySQL RDS 4 98
MSSQL 2008 with mySQL webservers 7 53
Insert with SET how to handle join 6 72
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

803 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