---------- Original Message -----------
From: "Tommi Prami" <groups.tprami@...>

> Hello,
> we would need something like this.
> select * from view_something where contractorid=10
> (let's say) if ContractorId is 10 we can select directly form Table-X.
> if ContractorId<>10 we must do much more complex join which would cause
> doublicate rows in case of contractorid=10.
> I am not too much of an SQL goy, but I can provide more information if need.
> But is there way to do two or more separate set's of SQL in view depending
> on the where clause, different joins etc...
> I think with UNION you can do this depending on the selected data, but how
> about the where clause. (stored progs are out of the question, unless it
> could be used trough view. select * from view_something where
> contractorid=10 would actually select data from proc_something hich would
> have some case or if which would deal case descripend (poorly) above).
>
> -TP-

------- End of Original Message -------

Could you provide a more complete description of the sorts of things you'd like
it to (not) do, depending on the scenario? Have you tried something, and had it
fail? If so, what was the problem? (Invalid sql, wrong results, poor
performance?) This would help us build examples that make sense for your
situation.

Yes, you could do:

union:
select ... where x = 10
union
select ... where x != 10

You could also do:

multi-conditional join:
select ... left join ... on x != 10 and ...
(but does that change anything? do you want to avoid a join when x = 10
because you already know there's no data to join to, or because there is, but
you don't want to join to it?)

and

sub-query:
select ..., case when x != 10 then (select ... from ...) else null end as ...

As of FB2.1, you cannot wrap a stored procedure with a view. That won't be
available (I think) until FB2.5. Even so, that wouldn't really help with
performance: the stored procedure would return all possible results, which
would then be filtered in the view; it can't really see what you put in your
WHERE clause when selecting from the view.

-Philip