Re: DECODE? IIF?

Cathy Morse (Cathy.Morse_at_ClemsonSC.ATTGIS.COM) wrote:
: I'm trying to pull data from 2 different fields, depending on
: the result of a comparison.
 

: It seems that decode won't let me do a "field < x".
: Is there an immediate IF statement in SQLPlus, like
: in Access, dBase, etc, etc, etc?
 

: Here's what I'm trying to do:
 

: Table
: Actual Quantity Planned Quantity Date
: 10 20 4/1/95
: 10 20 5/1/95
: 10 20 6/1/95
: 10 20 7/1/95
: 20 8/1/95
: 20 9/1/95
: 20 10/1/95
 

: Desired result:
: Date Quantity
: 4/1/95 10
: 5/1/95 10
: 6/1/95 10
: 7/1/95 10
: 8/1/95 20
: 9/1/95 20
: 10/1/95 20
 

: Assuming you ran this after 7/1 but before 8/1.
 

: I need to compare the DATE field to SYSDATE and pull either
: PLAN or ACTUAL.
 

: How do I do this?
 

: Thanks
:

: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
: All opinions expressed are my own...blah, blah, blah....
 

: SMILE! (It makes people wonder what you're up to!)
: <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

How about the following:

select decode(actual_qty(null,planned_qty,(least(actual_qty,planned_qty)),

       reference_date
from your_table
where < your date filtering criteria >

This returns planned_qty if actual_qty is null but returns the least of actual_qty and planned_qty if both have values.

I cannot guarantee this free of syntax errors, but the idea is there.

I hope this helps. Received on Thu Aug 10 1995 - 00:00:00 CEST