CODEWITHSUNDEEP
sqloracle-databasedecode
user8467219
user8467219

Reputation:

Oracle DECODE Syntax

Problem: I'm deciphering a query that as a function called DECODE. I looked up the syntax (Column, value, replacement value, default) but I do not undertand what the below syntax is doing.

Query:

WHERE 1=1
  AND DECODE(a1.prev_start_date, NULL, 'THIS IS NOT NULL', a1.prev_end_date) IS NOT NULL

When I add the above syntax the below row does not display, why?

Table:

ID       prev_start_date    prev_end_date
456        2/13/2017

Upvotes: 0

Views: 1298

Answers (2)

Patrick Bacon
Patrick Bacon

Reputation: 4640

Records with Date Effectivity

Typically when a record has a date effectivity (e.g. start and end date), the convention is to use the between condition where the end date has an NVL function call.

The principal is that a lot of effective date have not been end dated.

The check for a NULL around the start date as you showed is satisfied with this between condition (a NULL will be excluded).

With respect to the decode statement you displayed, it 

WHERE 1=1
  AND DECODE(a1.prev_start_date, NULL, 'THIS IS NOT NULL', a1.prev_end_date) IS NOT NULL

The decode statement can be read as:

"if start date is NULL, then 'THIS IS NOT NULL', else prev_end_date" which makes no sense.

I would have a between condition as follows:

SCOTT@db>WITH test AS
  2    (SELECT hiredate prev_start_date, sysdate prev_end_date FROM emp
  3    UNION ALL
  4    SELECT to_date(NULL,'dd-mon-yyyy'), to_date(NULL,'dd-mon-yyyy') FROM dual
  5    )
  6  SELECT prev_start_date,
  7    prev_end_date
  8  FROM test t
  9  WHERE 1=1
 10  AND sysdate between t.prev_start_date and NVL(t.prev_end_date, sysdate);
PREV_START_DATE          PREV_END_DATE            
17-DEC-1980 12:00:00 AM  27-SEP-2017 12:40:41 PM  
20-FEB-1981 12:00:00 AM  27-SEP-2017 12:40:41 PM  
22-FEB-1981 12:00:00 AM  27-SEP-2017 12:40:41 PM  
02-APR-1981 12:00:00 AM  27-SEP-2017 12:40:41 PM  
28-SEP-1981 12:00:00 AM  27-SEP-2017 12:40:41 PM  
01-MAY-1981 12:00:00 AM  27-SEP-2017 12:40:41 PM  
09-JUN-1981 12:00:00 AM  27-SEP-2017 12:40:41 PM  
09-DEC-1982 12:00:00 AM  27-SEP-2017 12:40:41 PM  
17-NOV-1981 12:00:00 AM  27-SEP-2017 12:40:41 PM  
08-SEP-1981 12:00:00 AM  27-SEP-2017 12:40:41 PM  
12-JAN-1983 12:00:00 AM  27-SEP-2017 12:40:41 PM  
03-DEC-1981 12:00:00 AM  27-SEP-2017 12:40:41 PM  
03-DEC-1981 12:00:00 AM  27-SEP-2017 12:40:41 PM  
23-JAN-1982 12:00:00 AM  27-SEP-2017 12:40:41 PM  


14 rows selected.

The NULL is excluded from the result set.

Upvotes: 0

user5683823
user5683823

Reputation:

Here is what that code snippet does:

If prev_start_date is NULL, then the function returns the string 'THIS IS NOT NULL'. That string is not NULL, so the condition evaluates to TRUE.

If prev_start_date is not NULL then DECODE() returns prev_end_date. The condition is TRUE only when prev_end_date is not NULL.

SO: the condition is equivalent to

(prev_start_date is null or prev_end_date is not null)

Obviously, in the example you gave, prev_start_date is not NULL, but prev_end_date is NULL - exactly the combination of values that will NOT pass the filter.

Upvotes: 2

Related Questions