I have a complex view in a SQL2K Database, made by another person.

Designer Screenshot:
http://freenet-homepage.de/schuttvim/With_Join.gif

In it there is a grafical join symbol which I would really like to understand.
It is a symbol which looks like an Outer Join icon with the character 'Fx' in it.
The ends of the connection are *not* attached to certain table fields but only to
the tables top bar.

To analyse this I then printed the SQL Code once *with* this join, then
*removed* the join in the designer and then printed the SQL once again.

Designer Screenshot:
http://freenet-homepage.de/schuttvim/Without_Join.gif

The SQL outcome is as follows. By removing the Fx-Join, the three lines at the
bottom marked "THIS LINE.. AND ALSO THIS.." are removed from the text.
As you can see in the text this join is not an ordinary join between the two
tables to which the grafical join is connected to.

SELECT Ve.Vertrags_Nr,
...
FROM dbo.Abnahmestellen_Adressen_ZuO AsAdrZuo
INNER JOIN dbo.Adressen Adr
ON AsAdrZuo.Adressen_key = Adr.Adressen_Key
INNER JOIN dbo.Zaehlpunkte ZP
INNER JOIN dbo.Zaehler Za
ON ZP.Zaehlpunktbezeichnung = Za.Zaehlpunktbezeichnung
INNER JOIN dbo.Vertraege Ve
INNER JOIN dbo.Kunden Ku
ON Ve.Kunden_Key = Ku.Kunden_Key
INNER JOIN dbo.Vertraege_Zaehlpunkte_Strom_ZuO VeZpZuO
ON Ve.Vertraege_Key = VeZpZuO.Vertraege_Key
ON ZP.Zaehlpunkte_Key = VeZpZuO.Zaehlpunkte_key
INNER JOIN dbo.Abnahmestellen AbS
ON ZP.Abnahmestellen_Key = AbS.AbnahmeStellen_Key
ON AsAdrZuo.Abnahmestellen_Key = AbS.AbnahmeStellen_Key
AND AsAdrZuo.Vertraege_Key = VeZpZuO.Vertraege_Key
LEFT OUTER JOIN dbo.Zaehlpunkte_Versorgung_Strom ZpVe
ON ZP.Zaehlpunkte_Key = ZpVe.Zaehlpunkte_key
AND VeZpZuO.Datum_von <= ZpVe.Datum_Versorgungsbeginn
AND (VeZpZuO.Datum_bis IS NULL         -- THIS LINE IS PART OF THE FX - JOIN
AND ZpVe.Datum_Versorgungsende IS NULL -- AND ALSO THIS ONE
OR VeZpZuO.Datum_bis >= ZpVe.Datum_Versorgungsbeginn) -- AND ALSO THIS ONE
LEFT OUTER JOIN dbo.Zaehlpunkte_Versorgung_Strom_Kuendigungen ZpVeKue
ON ZpVe.Zaehlpunkte_Versorgung_Strom_Key = ZpVeKue.Zaehlpunkte_Versorgung_Strom_key

  Questions:
  - What is the effect of this 'Fx' - Join ?
  - How does it work, how can one explain these join clauses in human language?

  Thank You very much

  Joachim

The effects of such a JOIN? If the expression involved transformations (e.g.
functions on predicates) then the Query Optimizer would not be able to take
advantage of any otherwise appropriate indexes when retrieveing data.

Have you compared the execution plans of the original vs. the "corrected"
queries?

ML

---
http://milambda.blogspot.com/

-----------------------------------------------Reply-----------------------------------------------