At 07:49 Tuesday, 15.07.2003 +0000, you wrote:

>FROM Markus Ostenried
>
>select *
> from customer
> where (type = :paramtype) or (Cast('*' as VarChar(3)) = :paramtype)
>
>I find this sql statement a master piece. You did a wonderful job
>Markus Ostenried. At first I really don't understand the sql after
>after a careful observation, I then understand what is does.
>
>But Iam not so sure what really happens internally.
>
>You use varchar right? I tried to use char and I got the same result,
>is their any difference from using VarChar from Char?

In my test I had to use the *exact* same type in the CAST as I had defined
my field with. See below.

>Before I used to use this trick. For example
>
>select *
>from customer
>where '1'='1'
>
> From what I learn is we can use this
>
>select *
>from customer
>where (type = :paramtype) or ('*' = :paramtype)
>
>If the type field has a column width of 1, right?

Yes.

>But what don't get is this ... cast('*' as varchar(3)) , before I
>just knew that it is use in the select part of the sql statement.

I have never used this kind of sql statement before. I just opened IB_SQL
and tried if it would work. Without the CAST IBO prepared two parameters
for me beacuse '*' is only a single char and my field is defined as
VarChar(3). So you have to tell the parser that it has to treat the
constant '*' like a VarChar(3). Then IBO will notice that it has already
created a parameter with this name and with the same type, so that one will
be used in the right part of the 'OR', too.

Type casting here is the same as in Delphi: In a ButtonClick event you can
write
TButton(Sender).Caption := 'bla';
Panel1.Visible := Boolean( 0 );
Of course this only works if type you convert to is compatible.

Since I'm not a SQL guru I don't know how fast it is to write such a sql
statement. I'm not sure if it can use indexes efficiently. If it is to slow
on large tables then you have to use the OnPrepareSQL event and only add a
WHERE clause when it's really needed.

HTH,
Markus