Board index » delphi » conditional fields in select

conditional fields in select

I have a query, in wich I would like to pass either a date or a number.

TABLE test
test_Id : integer
date: datetime
...

I'm trying to do something like this:

select * from test where
  ......a lot of inner join....
if test_id <> 0 then
  where test_id = :test_id
else
  where date > :date
end;

the purpose is to call a SELECT either with one or the other value passed as
parameter.

It's there any way to do it differently with one SQL request.???

Bob

 

Re:conditional fields in select


Quote
>if test_id <> 0 then
>  where test_id = :test_id
>else
>  where date > :date
>end;

Couldn't that be rewritten as:

where ((test_id <> 0) and (test_id = :test_id)) or
  ((test_id = 0) and (date > :date))

// David

Re:conditional fields in select


In article <3b822813_1@dnews>, "Bob Bedford" <bedfo...@nospamhotmail.com>
wrote:

Quote
> I have a query, in wich I would like to pass either a date or a number.

> TABLE test test_Id : integer date: datetime
> ...

> I'm trying to do something like this:

> select * from test where
>   ......a lot of inner join....
> if test_id <> 0 then
>   where test_id = :test_id
> else
>   where date > :date
> end;

> the purpose is to call a SELECT either with one or the other value
> passed as parameter.

> It's there any way to do it differently with one SQL request.???

not using plain SQL, you have to use (db specific) stored procedures to
do this.

regards,
        Olivier

Other Threads