Re: oracle 9.2 sql plus IF NULL

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 20 Feb 2004 09:52:13 GMT
Message-ID: <c14lcc$1ejpn3$1@ID-82536.news.uni-berlin.de>

> I am trying to write a select statement that will ask the user for a
> variable
>
> table
>
> Name number dept
> John 1 acc
> Iain 2 acc
> Martin 3 sales
> Bobby 4 IT
>
> Select * from table
>
> where dept = '&Input'
>
> The user can key in the dept but if it is blank all records are
> display. How do I code this

Colin

the following should do:

    set feedback off     

    create table t (

      name varchar2(10), 
      num  number,
      dept varchar2(10)

    );     

    insert into t values ('John', 1, 'acc');
    insert into t values ('Iain', 2, 'acc');
    insert into t values ('Marc', 3, 'sal');
    insert into t values ('Bob' , 4, 'it' );
    

    undefine dept     

    select * from t where dept =
    case when length('&&dept') > 0 then       '&dept'
    else
      dept
    end;

hth
Rene

drop table t;

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch

Received on Fri Feb 20 2004 - 03:52:13 CST