Re: DECODE? IIF?

Cathy Morse <Cathy.Morse_at_ClemsonSC.ATTGIS.COM> wrote:
>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?

Nope. But don't despair...

>I need to compare the DATE field to SYSDATE and pull either
>PLAN or ACTUAL.
>How do I do this?

Like this:
SQL> select * from b;

    ACT_QT PLN_QT AS_OF

• ---------- ---------

        10 20 01-APR-95         10 20 01-MAY-95         10 20 01-JUN-95         10 20 01-JUL-95         10 20 01-AUG-95         10 20 01-SEP-95         10 20 01-OCT-95         10 20 01-NOV-95 8 rows selected.

SQL> select
  2 decode(
  3 sign(trunc(sysdate,'MM')-trunc(as_of,'MM')),   4 -1, pln_qt, act_qt) Quantity,
  5 as_of
  6 from b
  7 order by as_of
  8 /

  QUANTITY AS_OF

• ---------

        10 01-APR-95         10 01-MAY-95         10 01-JUN-95         10 01-JUL-95         10 01-AUG-95         20 01-SEP-95         20 01-OCT-95         20 01-NOV-95 8 rows selected.

• Dave Sloan dsloan_at_us.oracle.com