Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Understanding Complex View with 'Fx' - Join in Designer


Hello,

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

Fx designates that the JOIN is not based on a simple column comparison but
rather based on an expression (e.g. a function).

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-----------------------------------------------

ML wrote:
> Fx designates that the JOIN is not based on a simple column comparison but
> rather based on an expression (e.g. a function).

Function/expression: That is what I thought first.
But the query onlz "consists" of the three lines I marked in the code,
which are each AND or OR - expansions of a JOIN clause stated before.
Maybe it is the OR part that makes the code an expression rather
than keeping a simple column comparison.

What I guess the join "does" is something like this:

...
-- do a join between ZP and ZpVe (only when) ...
LEFT OUTER JOIN dbo.Zaehlpunkte_Versorgung_Strom ZpVe
ON ZP.Zaehlpunkte_Key = ZpVe.Zaehlpunkte_key
-- ... these fields of VeZpZuO and ZpVe match <=
AND VeZpZuO.Datum_von <= ZpVe.Datum_Versorgungsbeginn
-- ... and the follwing complex AND / OR condition is met
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
...

My intellectual problem is that the conditions and clauses are so distributed over
several tables, even tables which are different to those to which the grafical
connection end points are connected to.

Can someone describe this join better than I tried in the snippet above?

Joachim

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc