Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL/Linux: Get Table Names from MySQL Dump 1 53
MySQL: Get All Duplicates (not grouped) 3 48
Create a Select Query and Populate a Table 3 43
MySQL Grouping 2 21
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

914 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

19 Experts available now in Live!

Get 1:1 Help Now