Decode on null
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Decode on null

  1. #1
    Join Date
    Dec 2003
    Posts
    90

    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;

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Feb 2004
    Posts
    77
    SELECT id, name
    FROM employees
    WHERE id < 1000
    AND (pStartDate is null or
    (pStartDate is not null and datecreated > StartDate)
    )

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Feb 2004
    Posts
    77
    Just added "pStartDate is not null" for documentation.

    makes query easier to understand.

  6. #6
    Join Date
    Dec 2003
    Posts
    90

    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?

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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).

  8. #8
    Join Date
    Dec 2003
    Posts
    90

    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.

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
  •  


Click Here to Expand Forum to Full Width