Hello, I am trying to modify a where clause to display every record when 'ALL' value is entered. All the records populate but not if the value is empty. I am using Toad for Oracle 12.6. Below is a test create table and insert statements.
--Heres the select statement to search the table but the where clause for INAME is not correct.
SELECT REQUEST_NUMBER,
R_COMPLETED,
M_COMPLETED,
TYPE,
RDATE,
ONAME,
INAME
FROM TBL_REQUEST_COMPLETED
WHERE RDATE BETWEEN TO_DATE ( :P37_START_DATE, 'MM/DD/YYYY')
AND NVL (TO_DATE ( :P37_END_DATE, 'MM/DD/YYYY'),
SYSDATE)
AND INSTR (
UPPER ("INAME"),
UPPER (
NVL (DECODE ( :P37_INSPECTOR, 'ALL', NULL, :P37_INSPECTOR),
"INAME"))) > 0
--Start Date = 01/01/2017
--End Date =
--INSPECTOR = ALL
--Only 5 rows will display but there is a total of 6 records. The record with the INAME empty does not display.
--Toad for Oracle 12.6
--SQL
CREATE TABLE TBL_REQUEST_COMPLETED
(
REQUEST_NUMBER NUMBER NOT NULL,
R_COMPLETED VARCHAR2 (15 BYTE),
M_COMPLETED VARCHAR2 (15 BYTE),
TYPE VARCHAR2 (15 BYTE),
RDATE DATE,
ONAME VARCHAR2 (15),
INAME VARCHAR2 (15 BYTE)
);
INSERT INTO TBL_REQUEST_COMPLETED (REQUEST_NUMBER,
R_COMPLETED,
M_COMPLETED,
TYPE,
RDATE,
ONAME,
INAME)
VALUES (1,
'COMPLETED',
'COMPLETED',
'BOTH',
TO_DATE ('02/01/2018 10:07:03', 'MM/DD/YYYY HH24:MI:SS'),
'JAMES',
'SED');
INSERT INTO TBL_REQUEST_COMPLETED (REQUEST_NUMBER,
R_COMPLETED,
M_COMPLETED,
TYPE,
RDATE,
ONAME,
INAME)
VALUES (7,
'COMPLETED',
'',
'R',
TO_DATE ('02/01/2018 10:07:03', 'MM/DD/YYYY HH24:MI:SS'),
'EDWARD',
'');
INSERT INTO TBL_REQUEST_COMPLETED (REQUEST_NUMBER,
R_COMPLETED,
M_COMPLETED,
TYPE,
RDATE,
ONAME,
INAME)
VALUES (8,
'',
'',
'M',
TO_DATE ('02/01/2018 10:07:03', 'MM/DD/YYYY HH24:MI:SS'),
'ROBERT',
'RICHARD');
INSERT INTO TBL_REQUEST_COMPLETED (REQUEST_NUMBER,
R_COMPLETED,
M_COMPLETED,
TYPE,
RDATE,
ONAME,
INAME)
VALUES (9,
'',
'COMPLETED',
'R',
TO_DATE ('02/01/2018 10:07:03', 'MM/DD/YYYY HH24:MI:SS'),
'JOHN',
'CHAD');
INSERT INTO TBL_REQUEST_COMPLETED (REQUEST_NUMBER,
R_COMPLETED,
M_COMPLETED,
TYPE,
RDATE,
ONAME,
INAME)
VALUES (12,
'COMPLETED',
'',
'M',
TO_DATE ('05/31/2018 08:21:27', 'MM/DD/YYYY HH24:MI:SS'),
'GREEN',
'CHAD');
INSERT INTO TBL_REQUEST_COMPLETED (REQUEST_NUMBER,
R_COMPLETED,
M_COMPLETED,
TYPE,
RDATE,
ONAME,
INAME)
VALUES (13,
'COMPLETED',
'',
'BOTH',
TO_DATE ('05/31/2018 08:21:37', 'MM/DD/YYYY HH24:MI:SS'),
'BROWN',
'CHAD');
COMMIT;