refactor: if() to CASE WHEN (#31360)
* refactor: if() to CASE WHEN
* fix: remove duplicate order by
* fix: remove extraneous table
* style: reformat to black spec
Co-authored-by: Ankush Menat <ankush@frappe.io>
diff --git a/erpnext/stock/doctype/pick_list/pick_list.py b/erpnext/stock/doctype/pick_list/pick_list.py
index 7dc3ba0..d31d695 100644
--- a/erpnext/stock/doctype/pick_list/pick_list.py
+++ b/erpnext/stock/doctype/pick_list/pick_list.py
@@ -699,7 +699,7 @@
AND `company` = %(company)s
AND `name` like %(txt)s
ORDER BY
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999), name
+ (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end) name
LIMIT
%(start)s, %(page_length)s""",
{
diff --git a/erpnext/stock/utils.py b/erpnext/stock/utils.py
index 6d8fdaa..9fb3be5 100644
--- a/erpnext/stock/utils.py
+++ b/erpnext/stock/utils.py
@@ -499,7 +499,7 @@
def get_incoming_outgoing_rate_for_cancel(item_code, voucher_type, voucher_no, voucher_detail_no):
outgoing_rate = frappe.db.sql(
- """SELECT abs(stock_value_difference / actual_qty)
+ """SELECT CASE WHEN actual_qty = 0 THEN 0 ELSE abs(stock_value_difference / actual_qty) END
FROM `tabStock Ledger Entry`
WHERE voucher_type = %s and voucher_no = %s
and item_code = %s and voucher_detail_no = %s