-
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;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
SELECT id, name
FROM employees
WHERE id < 1000
AND (pStartDate is null or
(pStartDate is not null and datecreated > StartDate)
)
-
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.
-
perfomance
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).
-
num records
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?
Last edited by luciffer; 02-27-2004 at 12:18 PM.
-
milliseconds to get a row, microseconds to do the arithmetic . . .
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|