I have a query and I want to use if statement inside where clause like this :
[code]
SELECT e.first_name,
e.last_name,
e.status
FROM employees e
WHERE IF status_flag = STATUS_ACTIVE then e.status = ‘A’
IF status_flag = STATUS_INACTIVE then e.status = ‘T’
IF source_flag = SOURCE_FUNCTION then e.business_unit = ‘production’
IF source_flag = SOURCE_USER then e.business_unit = ‘users’
AND e.first_name LIKE firstname
AND e.last_name LIKE lastname
AND e.employid LIKE employee_id;
[/code]
but I got error “ORA-00920: invalid relational operator”
hello oracle user,
you can use CASE statement same like IF ex:
[code]
WHERE e.status = (CASE WHEN status_flag = STATUS_ACTIVE THEN ‘A’
WHEN status_flag = STATUS_INACTIVE THEN ‘T’
ELSE null END)
AND e.business_unit = (CASE WHEN source_flag = SOURCE_FUNCTION THEN ‘production’
WHEN source_flag = SOURCE_USER THEN ‘users’
ELSE null END)
[/code]