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 Go to next message
Sekhar6617
Messages: 22
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

Report message to a moderator

CASE in where Clause [message #684639 is a reply to message #684638] Fri, 16 July 2021 08:27 Go to previous messageGo to next message
Sekhar6617
Messages: 22
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

Report message to a moderator

CASE in where Clause [message #684640 is a reply to message #684638] Fri, 16 July 2021 08:28 Go to previous messageGo to next message
Sekhar6617
Messages: 22
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

Report message to a moderator

CASE in where Clause [message #684641 is a reply to message #684638] Fri, 16 July 2021 08:28 Go to previous messageGo to next message
Sekhar6617
Messages: 22
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

Report message to a moderator

Re: CASE in where Clause [message #684642 is a reply to message #684638] Fri, 16 July 2021 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
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.

Report message to a moderator

Re: CASE in where Clause [message #684654 is a reply to message #684642] Sat, 17 July 2021 10:50 Go to previous message
Sekhar6617
Messages: 22
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

Report message to a moderator

Previous Topic: How to convert multi_byte to single_byte character
Next Topic: Issues with my Associative Array inside a Package spec
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 00:26:55 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.