Where 子句中的 if 语句

2021-12-24 00:00:00 oracle plsql plsqldeveloper

我正在处理一个在WHERE"子句中包含IF"语句的查询.但是 PLSQL Developer 在执行时出现了一些错误.任何人都可以帮助我进行正确的查询吗?这是查询:

I am working with a query which contains "IF" statements within a "WHERE" clause. But PLSQL Developer is giving some errors while executing it. Can anyone please help me with the correct query? Here is the query:

SELECT t.first_name,
       t.last_name,
       t.employid,
       t.status
  FROM employeetable t
 WHERE IF status_flag = STATUS_ACTIVE then t.status = 'A'
       IF status_flag = STATUS_INACTIVE then t.status = 'T'
       IF source_flag = SOURCE_FUNCTION then t.business_unit = 'production'
       IF source_flag = SOURCE_USER then t.business_unit = 'users'
   AND t.first_name LIKE firstname
   AND t.last_name LIKE lastname
   AND t.employid LIKE employeeid;

我收到错误ORA-00920:关系运算符无效".

I receive the error "ORA-00920: invalid relational operator".

在 status_flag = STATUS_ACTIVE 周围放置括号会导致错误ORA-00907:缺少右括号"

Placing brackets around status_flag = STATUS_ACTIVE results in error "ORA-00907: missing right parenthesis"

推荐答案

CASE 或许能帮到你:

CASE might help you out:

SELECT t.first_name,
       t.last_name,
       t.employid,
       t.status
  FROM employeetable t
 WHERE t.status = (CASE WHEN status_flag = STATUS_ACTIVE THEN 'A'
                        WHEN status_flag = STATUS_INACTIVE THEN 'T'
                        ELSE null END)
   AND t.business_unit = (CASE WHEN source_flag = SOURCE_FUNCTION THEN 'production'
                               WHEN source_flag = SOURCE_USER THEN 'users'
                               ELSE null END)
   AND t.first_name LIKE firstname
   AND t.last_name LIKE lastname
   AND t.employid LIKE employeeid;

CASE 语句 评估多个条件以生成单个值.因此，在第一次使用时，我检查 status_flag 的值，根据它的值返回 'A'、'T' 或 null，并将其与 t.status 进行比较.我使用第二个 CASE 语句对 business_unit 列执行相同操作.

The CASE statement evaluates multiple conditions to produce a single value. So, in the first usage, I check the value of status_flag, returning 'A', 'T' or null depending on what it's value is, and compare that to t.status. I do the same for the business_unit column with a second CASE statement.

相关文章