[T-SQL] Join probleem

[baskabas]

Ik heb bij M$ SQL Server 2000 SP2 een probleem met een join...

Ik heb de volgende entiteiten in mijn datamodel (hoog naar laag)
t_onderlinge l -> t_relatie r -> t_polis p -> t_object o -> t_schade s

Voorheen liepen de relaties van l naar s, maar pas geleden is de relatie tussen o en s weggehaald... met als gevolg dat ik de stored proc's (sp) weer aan kon passen...

Nu heb ik echter een probleem met 1 sp...
Omdat ik gegevens uit zowel o als s nodig heb, heb ik een RIGHT JOIN gemaakt tussen deze tabellen, zodat waar mogelijk de gegevens uit o bij de gegevens uit s toegevoegd kunnen worden... en dan in de relaties naar boven werken om uiteindelijk ook nog wat van r toe te voegen.

Dit is de sp:

code:

CREATE PROCEDURE Sori_RapSchade
    @coll_nr  INT, 
    @vlmcht_nr char(4) ,
    @jaarmaand Decimal(6,0),
    @bukode char(2),
    @bu_srt_code char(3),
    @blnSto Bit
 AS

 SELECT distinct
   s.coll_nr                      AS Collectiviteitsnummer
  ,s.vlmcht_nr                  AS Volmachtsnummer
  ,s.bu_kode                      AS [BU-Kode]
  ,s.rubr_srt_kode                AS [Rubriek-kode]
  ,s.scha_nr                      AS Schadenummer
  ,s.pol_nr                    AS Polisnummer
  ,IsNull(r.naam     ,'<<geen relatie>>') AS Relatienaam 
  ,IsNull(r.postkode   ,'0000XX'        ) AS [Relatie postcode] 
  ,IsNull(r.huis_nr    ,0            ) AS [Relatie huisnummer] 
  ,IsNull(o.verz_waarde,0            ) AS [Verzekerde som] 
  ,IsNull(o.obj_nr     ,0            ) AS [Obj Vlgnr] 
  ,s.datum                      AS [Schade datum] 
  ,{ fn YEAR(s.datum) }            AS [Schade jaar] 
  ,dg.gbeur_oms                  AS Gebeurtenis 
  ,dg.oorz_oms                  AS Oorzaak 
  ,s.belang                    AS Belang 
  ,s.betaald                      AS Betaald 
  ,s.belang - s.betaald         AS Voorziening 
  ,s.betaald_fac                    AS [Betaald herverz faculatief]
  ,s.belang_fac  - s.betaald_fac        AS [Voorziening herverz facultatief]
 FROM 
   t_schade   s
  ,t_object   o
  ,t_polis    p
  ,t_relatie  r
  ,tr_dim_gos dg 
 WHERE s.coll_nr      =  @coll_nr
   AND s.vlmcht_nr  =  @vlmcht_nr
   AND s.jaar_maand     =  @jaarmaand
   AND s.bu_kode      =  @bukode
   AND s.rubr_srt_kode  =  @bu_srt_code
   AND dg.dim_gos_key   =  s.dim_gos_key
   AND dg.gbeur_id     < > 'STO'
   AND s.pol_nr   *=  o.pol_nr
   AND s.obj_nr   *=  o.obj_nr
   AND s.jaar_maand    *=  o.jaar_maand
   AND p.pol_nr    =  o.pol_nr
   AND p.jaar_maand     =  o.jaar_maand
   AND r.rel_nr    =  p.rel_nr
   AND r.jaar_maand     =  p.jaar_maand

GO

Als ik deze probeer te runnen krijg een een foutmelding:

Server: Msg 303, Level 16, State 1, Procedure Sori_RapSchade, Line 10
The table 't_object' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

Nou... daar ben ik het dus niet mee eens!

Ik heb daarna het hele ding nog in een smerige view gegooid zodat de joins naar FROM-clause verplaats werden, omdat een andere DBA van mening was dat M$ SQL Server niet goed met Joins in de WHERE-clause om kan gaan....

Ik krijg dan idd geen foutmelding... maar ook geen records... en ze zijn er, dat weet ik zeker, want als ik de joins van hoog naar laag leg komen er gewoon records uit!

ps. Ik weet natuurlijk wel dat ik dit met een tijdelijke tabel of iets dergelijks op kan lossen, maar daar gaat het nu even niet om... ik wil het in 1 query oplossen... als een van jullie zeker weet dat het simpelweg niet kan, dan wil dat natuurlijk ook graag weten, maar dan wel met reden natuurlijk!

[baskabas]

Sjonge jonge... wat ben ik weer slim bezig

Ik kom er net achter dat veld s.vlmcht_nr niet overal ingevuld is... ... overigens wel heel toevallig dat ik dan niks terug krijg, want normaal gesproken krijg ik iets van 200 records terug, maar bij dit ongelukkig gekozen test scenario maar 4... en daar was dan ook net ff geen volmachtnummer ingevuld...

Het werk overigens nu alleen met de Joins in de FROM-clause ipv de WHERE-clause:

code:

 SELECT distinct
   s.coll_nr                      AS Collectiviteitsnummer
  ,s.vlmcht_nr                  AS Volmachtsnummer
  ,s.bu_kode                      AS [BU-Kode]
  ,s.rubr_srt_kode                AS [Rubriek-kode]
  ,s.scha_nr                      AS Schadenummer
  ,s.pol_nr                    AS Polisnummer
  ,IsNull(r.naam     ,'<<geen relatie>>') AS Relatienaam 
  ,IsNull(r.postkode   ,'0000XX'        ) AS [Relatie postcode] 
  ,IsNull(r.huis_nr    ,0            ) AS [Relatie huisnummer] 
  ,IsNull(o.verz_waarde,0            ) AS [Verzekerde som] 
  ,s.obj_nr                    AS [Obj Vlgnr] 
  ,s.datum                      AS [Schade datum] 
  ,{ fn YEAR(s.datum) }            AS [Schade jaar] 
  ,dg.gbeur_oms                  AS Gebeurtenis 
  ,dg.oorz_oms                  AS Oorzaak 
  ,s.belang                    AS Belang 
  ,s.betaald                      AS Betaald 
  ,s.belang - s.betaald         AS Voorziening 
  ,s.betaald_fac                    AS [Betaald herverz faculatief]
  ,s.belang_fac  - s.betaald_fac        AS [Voorziening herverz facultatief]
 FROM 
   t_polis p
    INNER JOIN t_object o ON 
       p.pol_nr = o.pol_nr 
     AND p.jaar_maand  = o.jaar_maand 
    INNER JOIN t_relatie r ON 
       p.rel_nr = r.rel_nr
     AND p.jaar_maand  = r.jaar_maand 
    RIGHT OUTER JOIN t_schade s ON
       o.jaar_maand  = s.jaar_maand 
     AND o.pol_nr   = s.pol_nr 
     AND o.obj_nr   = s.obj_nr
    INNER JOIN tr_dim_gos dg ON
       s.dim_gos_key = dg.dim_gos_key
 WHERE s.coll_nr      =  @coll_nr
   AND s.vlmcht_nr  =  @vlmcht_nr
   AND s.jaar_maand     =  @jaarmaand
   AND s.bu_kode      =  @bukode
   AND s.rubr_srt_kode  =  @bu_srt_code
   AND dg.gbeur_id     < > 'STO'

Ik heb de Joins nu maar letterlijk overgenomen uit dat View tooltje, want ik snap nog niet helemaal hoe dat in elkaar steekt qua syntax... vooral de eerste tabel p... waarom staat die als enige daar?

Ik check zo ook de T-SQL Help van M$ SQL Server wel ff om te kijken of dat verhelderend is...

Ik vindt het trouwes wel raar dat ie die vorige query qua syntax niet pikt... is gelijk aan deze...
Weet iemand daar een reden voor?

[Verwijderd]

select * from a, b, c where (clause)

is anders dan:

select * from a inner join b on (clause) inner join c (clause)

want de volgorde van de joins is bij de 2e versie gespecificeerd.

De 1e moet ook geldig zijn wanneer je b aan c plakt en dan daarna a daaraan vast.

Dat liep mis in je 1e query.

Overigens heeft die DBA ongelijk, 'niet goed' is niet van toepassing op SQLServer, maar op de query die gebouwd is.

Views gebruiken is altijd aan te bevelen, want SQLServer is geoptimaliseerd voor views, omdat je daar veel werk achter de schermen mee bespaart.

[baskabas]

select * from a, b, c where (clause)

is anders dan:

select * from a inner join b on (clause) inner join c (clause)

want de volgorde van de joins is bij de 2e versie gespecificeerd.

De 1e moet ook geldig zijn wanneer je b aan c plakt en dan daarna a daaraan vast.

Dat liep mis in je 1e query.

Dus het moet zo lukken?

code:

 WHERE s.coll_nr      =  @coll_nr
   AND s.vlmcht_nr  =  @vlmcht_nr
   AND s.jaar_maand     =  @jaarmaand
   AND s.bu_kode      =  @bukode
   AND s.rubr_srt_kode  =  @bu_srt_code
   AND dg.dim_gos_key   =  s.dim_gos_key
   AND dg.gbeur_id     < > 'STO'
   AND p.pol_nr    =  o.pol_nr
   AND p.jaar_maand     =  o.jaar_maand
   AND r.rel_nr    =  p.rel_nr
   AND r.jaar_maand     =  p.jaar_maand
   AND s.pol_nr   *=  o.pol_nr
   AND s.obj_nr   *=  o.obj_nr
   AND s.jaar_maand    *=  o.jaar_maand

^{(nogsteeds dezelfde foutmelding...)}

Views gebruiken is altijd aan te bevelen, want SQLServer is geoptimaliseerd voor views, omdat je daar veel werk achter de schermen mee bespaart.

imho alleen voor 'kleine' toepassingen... je kan ook veel te weinig met een view... maar tis idd makelijk om ff quick & dirty de grootste berg tekst van je uiteindelijke stored procedure te maken ja.

[Verwijderd]

Op maandag 29 april 2002 15:27 schreef TeknoGecko het volgende:

[..]

Dus het moet zo lukken?

nee. Je moet in je FOR clause de volgorde van je joins opgeven. Niet met comma's.

imho alleen voor 'kleine' toepassingen... je kan ook veel te weinig met een view... maar tis idd makelijk om ff quick & dirty de grootste berg tekst van je uiteindelijke stored procedure te maken ja.

Views zijn er juist voor grote toepassingen: bijna iedere join die je doet in een query is beter af met een view. Een view is een preselect, een precalc, op de data waar je je manipulaties of specifieke selects op los laat. Daarom zijn ze zo krachtig. Stored procs worden wel gecompileerd en je wint er wel veel door, maar stored procs zouden feitelijk logica op views moeten inhouden. dan zie je pas echt de power van SQLServer en de reden waarom hij alle lijsten van de TPC aanvoert.

[baskabas]

Nee. Je moet in je FOR clause de volgorde van je joins opgeven. Niet met comma's.

FOR?? Typo waarschijnlijk?

Het kan dus niet op mijn eerste manier... jammer...

Views zijn er juist voor grote toepassingen: bijna iedere join die je doet in een query is beter af met een view. Een view is een preselect, een precalc, op de data waar je je manipulaties of specifieke selects op los laat. Daarom zijn ze zo krachtig. Stored procs worden wel gecompileerd en je wint er wel veel door, maar stored procs zouden feitelijk logica op views moeten inhouden. dan zie je pas echt de power van SQLServer en de reden waarom hij alle lijsten van de TPC aanvoert.

Hmm... interresant... hoe pakt ie dat dan aan bij grote aggregaten data waar je dan selects op uitvoerd? Gaat hij dan niet eerst alles ophalen? Of past ie de criteria direct toe op de uit te voeren query?

In het laatste geval moet dat dan wel dynamisch gebeuren... dat zou dus performance verlies betekenen...

Tis in mijn situatie waarschijnlijk niet erg toepasbaar om views te gebruiken, omdat er gewoon te complexe berekeningen over verschillende niveau's lopen... dan zou je voor al die niveau's views moeten maken die eigenlijk weer te veel ophalen waardoor het niet performt óf je moet weer een hele berg aanmaken... maar dan raak je snel de draad kwijt vrees ik...

Maar ik zal eens wat tests gaan doen... kijken wat het best performt.

[Verwijderd]

Op maandag 29 april 2002 16:07 schreef TeknoGecko het volgende:

[..]

FOR?? Typo waarschijnlijk?

erm.. ja Dat moet 'FROM' zijn

Hmm... interresant... hoe pakt ie dat dan aan bij grote aggregaten data waar je dan selects op uitvoerd? Gaat hij dan niet eerst alles ophalen? Of past ie de criteria direct toe op de uit te voeren query?

De execution plans voor een query liggen al vast en hij optimized alles vooraf, dus wanneer de data in de tables wordt gestored. Wanneer je dus een query doet op een view, is dat bijna even snel als een query met een select op een table. Dit wordt met de query sneller ivm caching en statistics die worden gebruikt voor het verder optimizen van een view.

In het laatste geval moet dat dan wel dynamisch gebeuren... dat zou dus performance verlies betekenen...

Nee het is alleen performance verlies bij storen van data, niet bij het opvragen van data

Tis in mijn situatie waarschijnlijk niet erg toepasbaar om views te gebruiken, omdat er gewoon te complexe berekeningen over verschillende niveau's lopen... dan zou je voor al die niveau's views moeten maken die eigenlijk weer te veel ophalen waardoor het niet performt óf je moet weer een hele berg aanmaken... maar dan raak je snel de draad kwijt vrees ik...

Hmmm, nou je hebt een vaststaande relatie tussen je entiteiten, verdeeld over verschillende tables. Je zou een view kunnen maken die een resultset oplevert met alle data op 1 row. Dit worden wel veel rows, maar het heeft als voordeel dat SQLServer de query apart kan optimizen als view en niet per stored proc waar die select in zit. Je stored proc doet een simpele select op de view en past logic toe op de results van die select.