Where子句中的if语句 [英] If statement within Where clause

问题描述

我正在使用在"WHERE"子句中包含"IF"语句的查询.但是PL \ SQL Developer在执行它时会给出一些错误.有人可以帮助我提供正确的查询吗?这是查询:

I am working with a query which contains "IF" statements within a "WHERE" clause. But PL\SQL 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"

推荐答案

案例可能会帮助您:

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.

这篇关于Where子句中的if语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！