Decode on null

Hi i want to know if it is possible to use decode to do the following:

I have a pl/sql sp that takes a date (StartDate) as an in parameter. I always want the where clause to have the id < 1000 part, but i also want it so that if StartDate is not null the where clause will be

where employees.datecreated > startdate AND id < 1000

if start date is null then it would just be

where id < 1000

I've started with something like this...

SELECT id, name
FROM employees
WHERE DECODE (StartDate, Null, '',
employees.datecreated > TO_DATE(StartDate, 'dd-MM-yyyy') AND )
id < 1000;

SELECT id, name
FROM employees
WHERE datecreated > NVL(StartDate, datecreated-1)
AND id < 1000;

Or if you realy want to use DECODE thar badly, you could do

SELECT id, name
FROM employees
WHERE datecreated > DECODE(StartDate, null, datecreated-1, StartDate)
AND id < 1000;

SELECT id, name
FROM employees
WHERE id < 1000
AND (pStartDate is null or
(pStartDate is not null and datecreated > StartDate)
)

Quote:

---

Originally posted by oracle_faq
SELECT id, name
FROM employees
WHERE id < 1000
AND (pStartDate is null or
(pStartDate is not null and datecreated > StartDate)
)

---

I think the "pStartDate is not null" is redundant, no?

Just added "pStartDate is not null" for documentation.

makes query easier to understand.

Ok so whats the performance costs of teh following three options

SELECT id, name
FROM employees
WHERE datecreated > NVL(StartDate, datecreated-1)
AND id < 1000;

This one posted by jmodic, if StartDate is null this way will still do a comparison at every record for datecreated > datecreated-1 (it will always be true but still a comparison right?)

SELECT id, name
FROM employees
WHERE id < 1000
AND (pStartDate is null or
(pStartDate is not null and datecreated > StartDate)
)
This one posted by oracle_faq is similar isn't it, it will still do a comparison every time?

Something like this
Select id, name
from employees
where DECODE(StartDate, null, do nothing
not null, datecreated > StartDate AND)
id < 1000

If start date is null then do nothing (no comparison) else do the comparison. Then there isn't the extra costs of a comparison at every record if StartDate is null?

AFAIK none of them can use an index on the dates. If id is an id (i.e. a primary key) then only 1000 rows should be retrieved. So my prediction is that the difference in executing the different versions of the date logic will not be noticable (perhaps measurable if you insist).

for arguments sake change it to "id in (some group of 20,000 ids)"

doing datecreated > datecreated-1 still has to resolve datecreated-1 and do a comparison of the two.

Not doing that comparison on every record has got to make a difference, doesn't it?

milliseconds to get a row, microseconds to do the arithmetic . . .