在Oracle SQL查询中使用嵌套大小写条件的问题

Question

我有一个场景，在其中，我需要使用包计算员工的工资，如果有一些预付款，它会给我永久员工的剩余工资，然后使用另一个包将计算值转换为员工指定的货币。

我是这样做的:

CASE
  WHEN ( ai.employee_type = 'PERMANENT' and ai.payment_status_flag='Y')
  THEN EMP_UTILS_PKG.get_pay_amount_remaining( ai.employee_id )
  ELSE ail.amount_remaining
END amount_remaining,
CASE
  WHEN ( ai.salary_currency_code = NVL(find_currency('EMP_COMMON_CURRENCY'),ai.EMP_CURRENCY_CODE))
  THEN CASE
             WHEN ( ai.employee_type = 'PERMANENT' and ai.payment_status_flag='Y')
                THEN EMP_UTILS_PKG.get_pay_amount_remaining( ai.employee_id )
             ELSE ail.amount_remaining
  END
  ELSE EMP_API.convert_closest_amount_sql( x_from_currency => ai.EMP_CURRENCY_CODE ,x_to_currency => find_currency('EMP_COMMON_CURRENCY') ,x_amount =>( CASE
             WHEN ( ai.employee_type = 'PERMANENT' and ai.payment_status_flag='Y')
                THEN EMP_UTILS_PKG.get_pay_amount_remaining( ai.employee_id )
             ELSE ail.amount_remaining
END) ,x_max_roll_days => -1)
END EMP_CONVERTED_AMOUNT_REMAINING, 

我在我的查询中重复了以下条件3次:

WHEN ( ai.employee_type = 'PERMANENT' and ai.payment_status_flag='Y')
   THEN EMP_UTILS_PKG.get_pay_amount_remaining( ai.employee_id )
   ELSE ail.amount_remaining

是否有更简单的方法，我们可以避免重复这样的代码吗?

Answer 1

您已经使用了函数EMP_UTILS_PKG.get_pay_amount_remaining，那么为什么不创建一个PL/SQL函数来封装逻辑，然后在您的多个地方调用它呢?

Answer 2

您可以在FROM子句中使用内联视图来简化此操作。在您的活动代码中正确使用连接

SELECT amount_remaining
     , CASE
          WHEN ai.salary_currency_code = NVL(find_currency('EMP_COMMON_CURRENCY')
                                            ,ai.EMP_CURRENCY_CODE)
               THEN amount_remaining
          ELSE EMP_API.convert_closest_amount_sql(
                   x_from_currency => ai.EMP_CURRENCY_CODE
                  ,x_to_currency => find_currency('EMP_COMMON_CURRENCY')
                  ,x_amount => amount_remaining
                  ,x_max_roll_days => -1)
        END EMP_CONVERTED_AMOUNT_REMAINING,
FROM (SELECT CASE
               WHEN ai.employee_type = 'PERMANENT'
                AND ai.payment_status_flag='Y'
                    THEN EMP_UTILS_PKG.get_pay_amount_remaining( ai.employee_id )
               ELSE ail.amount_remaining
             END amount_remaining
        FROM table_name);