How to use user defined variable inside of `IF` expression?

Question

Some of my SQL statement like this:

  IF(timediff(a.wo_finish_time,
               IF(a.wo_failure_time <> '0000-00-00 00:00:00',
                  a.wo_failure_time,
                  a.wo_creation_time)) < 0,
      0,
      timediff(a.wo_finish_time,
               IF(a.wo_failure_time <> '0000-00-00 00:00:00',
                  a.wo_failure_time,
                  a.wo_creation_time)))

As you can see, it is verbose because part of it appears twice. So I tried user defined variable like this:

 if(@down_time := timediff(a.wo_finish_time,
                             IF(a.wo_failure_time <> '0000-00-00 00:00:00',
                                a.wo_failure_time,
                                a.wo_creation_time)) < 0,
      0,
      @down_time)

Maybe due to the difference in variable scope, it doesn't work. I know I can put this @down_time variable declaration in SELECT list, but that would also add it to the output column. I guess there must be a better way, so I post this question, hoping to find a better approach.

Tim Biegeleisen · Accepted Answer · 2017-07-10 08:23:30Z

You could use the following GREATEST trick:

SELECT GREATEST(0, TIMEDIFF(a.wo_finish_time,
                       IF(a.wo_failure_time <> '0000-00-00 00:00:00',
                       a.wo_failure_time,
                       a.wo_creation_time)))
FROM yourTable

Another workaround not requiring dynamic SQL would be to wrap your current query and then subquery out the correct value, something like this:

SELECT
    CASE WHEN t.col < 0 THEN 0 ELSE t.col END AS col
FROM
(
    SELECT TIMEDIFF(a.wo_finish_time,
           IF(a.wo_failure_time <> '0000-00-00 00:00:00',
              a.wo_failure_time,
              a.wo_creation_time)) AS col
           -- other columns?
    FROM yourTable
) t

If you were already using such a subquery or performance were not critical, then this second option might make sense.

Thanks. Glad to know GREATEST() function. Although the intention is not so obvious as IF(), it is a clever trick.

Collectives™ on Stack Overflow

How to use user defined variable inside of `IF` expression?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related