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

Rich Doughty <rich@xxxxxxxxxx> · Mon, 12 Dec 2005 17:32:47 +0000

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