IIF in postgres
You'll need to switch the logic over to a CASE expression. CASE expression are standard for most RDBMS's so it's worth learning. In your case (pun intended) it would translate to:
CASE
WHEN labor_sort_1.sortby_employeeid = 3721
THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 29 * labor_sort_1.number_of_ops
WHEN labor_sort_1.sortby_employeeid = 3722
THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 24 * labor_sort_1.number_of_ops
WHEN labor_sort_1.sortby_employeeid = 3755
THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 24 * labor_sort_1.number_of_ops
ELSE
(labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 17 * labor_sort_1.number_of_ops)
END AS labor_cost
Which is a lot cleaner looking since you don't have to monkey with nested iif()
issues and all that and should you need to add more employeeid's to the list of hard-coded labor costs, it's no biggie.
You might also find it advantageous to us the IN
condition here to so you only need two WHEN
clauses:
CASE
WHEN labor_sort_1.sortby_employeeid = 3721
THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 29 * labor_sort_1.number_of_ops
WHEN labor_sort_1.sortby_employeeid IN (3722, 3755)
THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 24 * labor_sort_1.number_of_ops
ELSE
(labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 17 * labor_sort_1.number_of_ops)
END AS labor_cost
Also, you could move the CASE expression into the equation to just put out whatever number you wish to multiply by:
(labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime)
* 24
* CASE
WHEN labor_sort_1.sortby_employeeid = 3721 THEN 29
WHEN labor_sort_1.sortby_employeeid IN (3722,3755) THEN 24
ELSE 17
END
* labor_sort_1.number_of_ops AS labor_cost
((this is a Wiki, you can edit!))
Same as @Daniel's answer, but generalizing to any datatype.
CREATE or replace FUNCTION iIF(
condition boolean, -- IF condition
true_result anyelement, -- THEN
false_result anyelement -- ELSE
) RETURNS anyelement AS $f$
SELECT CASE WHEN condition THEN true_result ELSE false_result END
$f$ LANGUAGE SQL IMMUTABLE;
SELECT iif(0=1,1,2);
SELECT iif(0=0,'Hello'::text,'Bye'); -- need to say that string is text.
Good when you are looking for a public-snippets-library.
NOTE about IMMUTABLE
and "PLpgSQL vs SQL".
-
The
IMMUTABLE
clause is very important for code snippets like this, because, as said in the Guide: "allows the optimizer to pre-evaluate the function when a query calls it with constant arguments" -
PLpgSQL is the preferred language, except for "pure SQL". For JIT optimizations (and sometimes for parallelism) SQL can obtain better optimizations. Is something like copy/paste small piece of code instead of use a function call.
Important conclusion: this function, after optimizations, is so fast than the @JNevill's answer; it will compile to (exactly) the same internal representation. So, although it is not standard for PostgreSQL, it can be standard for your projects, by a centralized and reusable "library of snippets", like pg_pubLib.