Home » SQL & PL/SQL » SQL & PL/SQL » CASE in where Clause (4 merged) (SQL)
CASE in where Clause (4 merged) [message #684638] |
Fri, 16 July 2021 08:27 |
|
Sekhar6617
Messages: 24 Registered: March 2021
|
Junior Member |
|
|
Hi All,
I am facing problem when trying to add more CASE statements for date field in where clause of SQL query.
Scenario: As per the requirement, we can consider that Active employees will have end_date field Blank
where as for terminated employees this field would be always populated with their end date.
So using this end_date we can filer out active or Inactive employees.
CREATE TABLE EMPLOYEE_TBL
(EMPLID VARCHAR2(10),
EMPL_NAME VARCHAR2(10),
ADDRESS1 VARCHAR2(10),
END_DATE DATE);
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6781', 'JOHN', 'TEXAS', '2021-05-21');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1) VALUES ('6782', 'DAVID', 'TEXAS');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6783', 'REED', 'TEXAS', '2021-03-19');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6784', 'THOMAS', 'TEXAS', '2021-06-30');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1) VALUES ('6785', 'MILES', 'TEXAS');
Below is the SQL that I wrote and :INPUT_DATA is the my prompt value passing to the SQL Query. So when I select 'Active' for the input parameter it should display rows whose END_DATE field is Blank. Whereas when I select 'Inactive' for the input parameter it should display rows whose END_DATE field is not Blank. The default value for the input parameter is 'All' then it should display all (Active and Inactive) rows.
SELECT EMPLID, EMPL_NAME, ADDRESS1
FROM EMPLOYEE_TBL
WHERE 1 = 1
AND CASE WHEN :INPUT_DATA = 'Active' THEN END_DATE END IS NULL
With the above SQL it is working fine for Active employees, when ever am trying to add more such CASE statements SQL is not working.
Request you please help me to work for all scenarios.
Note: my Input parameter will have always 3 Values. They are 'All', 'Active', 'Inactive' and we can select only one value at atime.
Thank you.
Regards
Sekhar
|
|
|
CASE in where Clause [message #684639 is a reply to message #684638] |
Fri, 16 July 2021 08:27 |
|
Sekhar6617
Messages: 24 Registered: March 2021
|
Junior Member |
|
|
Hi All,
I am facing problem when trying to add more CASE statements for date field in where clause of SQL query.
Scenario: As per the requirement, we can consider that Active employees will have end_date field Blank
where as for terminated employees this field would be always populated with their end date.
So using this end_date we can filer out active or Inactive employees.
CREATE TABLE EMPLOYEE_TBL
(EMPLID VARCHAR2(10),
EMPL_NAME VARCHAR2(10),
ADDRESS1 VARCHAR2(10),
END_DATE DATE);
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6781', 'JOHN', 'TEXAS', '2021-05-21');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1) VALUES ('6782', 'DAVID', 'TEXAS');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6783', 'REED', 'TEXAS', '2021-03-19');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6784', 'THOMAS', 'TEXAS', '2021-06-30');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1) VALUES ('6785', 'MILES', 'TEXAS');
Below is the SQL that I wrote and :INPUT_DATA is the my prompt value passing to the SQL Query. So when I select 'Active' for the input parameter it should display rows whose END_DATE field is Blank. Whereas when I select 'Inactive' for the input parameter it should display rows whose END_DATE field is not Blank. The default value for the input parameter is 'All' then it should display all (Active and Inactive) rows.
SELECT EMPLID, EMPL_NAME, ADDRESS1
FROM EMPLOYEE_TBL
WHERE 1 = 1
AND CASE WHEN :INPUT_DATA = 'Active' THEN END_DATE END IS NULL
With the above SQL it is working fine for Active employees, when ever am trying to add more such CASE statements SQL is not working.
Request you please help me to work for all scenarios.
Note: my Input parameter will have always 3 Values. They are 'All', 'Active', 'Inactive' and we can select only one value at atime.
Thank you.
Regards
Sekhar
|
|
|
CASE in where Clause [message #684640 is a reply to message #684638] |
Fri, 16 July 2021 08:28 |
|
Sekhar6617
Messages: 24 Registered: March 2021
|
Junior Member |
|
|
Hi All,
I am facing problem when trying to add more CASE statements for date field in where clause of SQL query.
Scenario: As per the requirement, we can consider that Active employees will have end_date field Blank
where as for terminated employees this field would be always populated with their end date.
So using this end_date we can filer out active or Inactive employees.
CREATE TABLE EMPLOYEE_TBL
(EMPLID VARCHAR2(10),
EMPL_NAME VARCHAR2(10),
ADDRESS1 VARCHAR2(10),
END_DATE DATE);
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6781', 'JOHN', 'TEXAS', '2021-05-21');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1) VALUES ('6782', 'DAVID', 'TEXAS');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6783', 'REED', 'TEXAS', '2021-03-19');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6784', 'THOMAS', 'TEXAS', '2021-06-30');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1) VALUES ('6785', 'MILES', 'TEXAS');
Below is the SQL that I wrote and :INPUT_DATA is the my prompt value passing to the SQL Query. So when I select 'Active' for the input parameter it should display rows whose END_DATE field is Blank. Whereas when I select 'Inactive' for the input parameter it should display rows whose END_DATE field is not Blank. The default value for the input parameter is 'All' then it should display all (Active and Inactive) rows.
SELECT EMPLID, EMPL_NAME, ADDRESS1
FROM EMPLOYEE_TBL
WHERE 1 = 1
AND CASE WHEN :INPUT_DATA = 'Active' THEN END_DATE END IS NULL
With the above SQL it is working fine for Active employees, when ever am trying to add more such CASE statements SQL is not working.
Request you please help me to work for all scenarios.
Note: my Input parameter will have always 3 Values. They are 'All', 'Active', 'Inactive' and we can select only one value at atime.
Thank you.
Regards
Sekhar
|
|
|
CASE in where Clause [message #684641 is a reply to message #684638] |
Fri, 16 July 2021 08:28 |
|
Sekhar6617
Messages: 24 Registered: March 2021
|
Junior Member |
|
|
Hi All,
I am facing problem when trying to add more CASE statements for date field in where clause of SQL query.
Scenario: As per the requirement, we can consider that Active employees will have end_date field Blank
where as for terminated employees this field would be always populated with their end date.
So using this end_date we can filer out active or Inactive employees.
CREATE TABLE EMPLOYEE_TBL
(EMPLID VARCHAR2(10),
EMPL_NAME VARCHAR2(10),
ADDRESS1 VARCHAR2(10),
END_DATE DATE);
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6781', 'JOHN', 'TEXAS', '2021-05-21');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1) VALUES ('6782', 'DAVID', 'TEXAS');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6783', 'REED', 'TEXAS', '2021-03-19');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6784', 'THOMAS', 'TEXAS', '2021-06-30');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1) VALUES ('6785', 'MILES', 'TEXAS');
Below is the SQL that I wrote and :INPUT_DATA is the my prompt value passing to the SQL Query. So when I select 'Active' for the input parameter it should display rows whose END_DATE field is Blank. Whereas when I select 'Inactive' for the input parameter it should display rows whose END_DATE field is not Blank. The default value for the input parameter is 'All' then it should display all (Active and Inactive) rows.
SELECT EMPLID, EMPL_NAME, ADDRESS1
FROM EMPLOYEE_TBL
WHERE 1 = 1
AND CASE WHEN :INPUT_DATA = 'Active' THEN END_DATE END IS NULL
With the above SQL it is working fine for Active employees, when ever am trying to add more such CASE statements SQL is not working.
Request you please help me to work for all scenarios.
Note: my Input parameter will have always 3 Values. They are 'All', 'Active', 'Inactive' and we can select only one value at atime.
Thank you.
Regards
Sekhar
|
|
|
Re: CASE in where Clause [message #684642 is a reply to message #684638] |
Fri, 16 July 2021 10:42 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Your INSERT statements are not correct:
SQL> INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6781', 'JOHN', 'TEXAS', '2021-05-21');
INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6781', 'JOHN', 'TEXAS', '2021-05-21')
*
ERROR at line 1:
ORA-01861: literal does not match format string
The correct syntax is:
SQL> INSERT INTO EMPLOYEE_TBL (EMPLID, EMPL_NAME, ADDRESS1, END_DATE) VALUES ('6781', 'JOHN', 'TEXAS', date '2021-05-21');
1 row created.
You uselessly complicate the query trying to use CASE expression:
SQL> var input_data varchar2(10)
SQL> exec :input_data := 'Active'
PL/SQL procedure successfully completed.
SQL> SELECT EMPLID, EMPL_NAME, ADDRESS1
2 FROM EMPLOYEE_TBL
3 WHERE :INPUT_DATA = 'All'
4 OR ( :INPUT_DATA = 'Active' AND END_DATE IS NULL )
5 OR ( :INPUT_DATA = 'Inactive' AND END_DATE IS NOT NULL )
6 /
EMPLID EMPL_NAME ADDRESS1
---------- ---------- ----------
6782 DAVID TEXAS
6785 MILES TEXAS
2 rows selected.
SQL> exec :input_data := 'Inactive'
PL/SQL procedure successfully completed.
SQL> SELECT EMPLID, EMPL_NAME, ADDRESS1
2 FROM EMPLOYEE_TBL
3 WHERE :INPUT_DATA = 'All'
4 OR ( :INPUT_DATA = 'Active' AND END_DATE IS NULL )
5 OR ( :INPUT_DATA = 'Inactive' AND END_DATE IS NOT NULL )
6 /
EMPLID EMPL_NAME ADDRESS1
---------- ---------- ----------
6781 JOHN TEXAS
6783 REED TEXAS
6784 THOMAS TEXAS
3 rows selected.
SQL> exec :input_data := 'All'
PL/SQL procedure successfully completed.
SQL> SELECT EMPLID, EMPL_NAME, ADDRESS1
2 FROM EMPLOYEE_TBL
3 WHERE :INPUT_DATA = 'All'
4 OR ( :INPUT_DATA = 'Active' AND END_DATE IS NULL )
5 OR ( :INPUT_DATA = 'Inactive' AND END_DATE IS NOT NULL )
6 /
EMPLID EMPL_NAME ADDRESS1
---------- ---------- ----------
6781 JOHN TEXAS
6782 DAVID TEXAS
6783 REED TEXAS
6784 THOMAS TEXAS
6785 MILES TEXAS
5 rows selected.
|
|
|
Re: CASE in where Clause [message #684654 is a reply to message #684642] |
Sat, 17 July 2021 10:50 |
|
Sekhar6617
Messages: 24 Registered: March 2021
|
Junior Member |
|
|
Hi Michel,
Thanks for the solution. Somehow I got the CASE logic into my mind that time and started with that logic. However I felt shame after looking into your logic.
Appreciate your prompt response.
Thank you.
Regards
Sekhar
|
|
|
Goto Forum:
Current Time: Sat Jun 29 08:01:22 CDT 2024
|