Re: View with an outer join - is there any way to optimise

John McCawley wrote:
> You should be able to use my trick...the join that is giving you the
> problem is:
>
> SELECT *
>  FROM
>      tokens.ta_tokenhist h INNER JOIN
>      tokens.vw_tokens    t ON h.token_id = t.token_id
>  WHERE
>      h.sarreport_id = 9 ;
>
>
> ta_tokenhist is already part of your view, right?  So you should be able
> to include the sarreport_id as part of your view, and then restructure
> your query as:
>
>
> SELECT *
>  FROM
>      tokens.ta_tokenhist INNER JOIN
>      tokens.vw_tokens ON tokens.ta_tokenhist.token_id =
> tokens.vw_tokens.token_id
>  WHERE
>      tokens.vw_tokens.sarreport_id = 9 ;
>
> I removed the aliases because they confuse me ;)

i don't think i can do that. basically i want to run a variety of queries
on the vw_tokens view. for example, joins i hope to do may include:

   tokens.ta_tokenhist h INNER JOIN tokens.vw_tokens WHERE h.customer_id = ?
   tokens.ta_tokenhist h INNER JOIN tokens.vw_tokens WHERE h.histdate between 'then' and 'now'
   tokens.vw_tokens WHERE number = ?

i just want vw_tokens to give me a constant resultset. i have a feeling
though that views aren't go to be able to give me what i need.

i suppose i could go for a set returning function, or just write the
queries manually.


--

   - Rich Doughty