At 01:49 PM 12/12/2003 +0800, you wrote:

>I could like to know if which is a better solution. Will there be a big
>difference in performance if I use view in Tib_query instead of a normal
>sql select.
>
>My sql is this:
>
>select * from "invoice"
>where "Type" in (select ID from "expensetype")

A view isn't necessary - it does nothing that a straight query doesn't
do. All you need is
select <column-list-please> from "invoice"
where "Type" in (select ID from "expensetype");

(or, for a finer drill-down, use
where "Type" = :"Type";
and Keylink to the type id from the main row from a lookup query
"select ID, description from "expensetype")

>If the view could be a better solution ... the view could derived from
>the sql above. As of now I do things without using view, as long as the
>sql is simple. What is the sql is compose of a lot of left joins what is
>the better solution then.

Generally speaking, there's no need to use views unless you have to
"virtualise" an intermediate set to enable you to reach data that you
cannot get with a single statement.

The work that optimizer has to do with views is more complex than with
direct tables, because it has to create extra levels of images for joins
and comparisons.

Views are great for getting "awkward" sets like groupings, re-entrant
unions and such but it's "taking the mountain road" for a regular operation.

You can't modify views, either; so anything that has to be changed means
breaking the task and reconstructing it.

HB