Discussion :

[Yann]

Environnement : SQL Server 2000

Objectif : concevoir une vue présentant en colonnes les lignes d'une table en utilisant une auto-jointure.

Contexte : une table historique contient des enregistrements marquant un évènement "début" et un évènement "fin". Les enregistrements "début" et "fin" ont la même structure, puisqu'elle sont hébergées dans la même table. La valeur d'une colonne permet de les différencier. Toutes les lignes "début" n'ont pas nécessairement de ligne "fin".

Structure de la table historique :
CREATE TABLE [APX_HISTO] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[objet_type] [int] NOT NULL ,
[op_type] [int] NOT NULL ,
[dossier_id] [varchar] (11) NOT NULL ,
[objet_id] [varchar] (50) NOT NULL ,
[detail_1] [varchar] (500) NULL ,
[detail_2] [varchar] (500) NULL ,
[creation_dt] [datetime] NOT NULL ,
CONSTRAINT [PK_M1_HISTO] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
)

Données de la table historique : cf. "table_histo.htm" en PJ

Structure souhaitée de la vue : cf. "vue_histo.htm" en PJ

Structure actuelle de la vue :

CREATE VIEW dbo.APX_Changement_Offre_V
AS

SELECT A.dossier_id AS CONTRAT,
A.detail_1 AS OFFRE,
A.creation_dt AS DEBUT,
B.creation_dt AS FIN,
A.objet_id AS INCREMENT
FROM dbo.APX_HISTO A,
dbo.APX_HISTO B
WHERE A.objet_type = 6
AND A.op_type = 1
AND (B.objet_type = 6 OR B.objet_type is null)
AND (B.op_type = 3 OR B.op_type IS NULL)
AND A.objet_id *= B.objet_id
AND A.dossier_id *= B.dossier_id

--> pour un dossier donné, 3 lignes de résultat

Problème :

1) la syntaxe utilisée pour la jointure n'est pas conforme à SQL2 et n'est pas pérène,
2) une fois portée vers une syntaxe LEFT OUTER JOIN, le résultat n'est pas le même qu'avec l'ancienne syntaxe "*=" :

SELECT A.dossier_id AS CONTRAT,
A.detail_1 AS OFFRE,
A.creation_dt AS DEBUT,
B.creation_dt AS FIN,
A.objet_id AS INCREMENT
FROM dbo.APX_HISTO A
LEFT OUTER JOIN dbo.APX_HISTO B
ON A.objet_id = B.objet_id
AND A.dossier_id = B.dossier_id
WHERE A.objet_type = 6
AND A.op_type = 1
AND (B.objet_type = 6 OR B.objet_type is null)
AND (B.op_type = 3 OR B.op_type IS NULL)

--> pour un dossier donné, 1 seule ligne de résultat

Demande : comment utiliser la syntaxe LEFT OUTER JOIN pour obtenir le même résultat qu'avec "*=" ?

[LoveBuzz]

Bonjour,

Je crois que celle-ci doit marcher:

SELECT A.dossier_id AS CONTRAT,
A.detail_1 AS OFFRE,
A.creation_dt AS DEBUT,
B.creation_dt AS FIN,
A.objet_id AS INCREMENT
FROM dbo.APX_HISTO A
LEFT OUTER JOIN dbo.APX_HISTO B
ON A.objet_id = B.objet_id
AND A.dossier_id = B.dossier_id
AND B.objet_type = 6
WHERE A.objet_type = 6
AND A.op_type = 1
AND (B.op_type = 3 OR B.op_type IS NULL)

A+

[LoveBuzz]

J'ai oublié un morceau

Là c'est mieux:
SELECT A.dossier_id AS CONTRAT,
A.detail_1 AS OFFRE,
A.creation_dt AS DEBUT,
B.creation_dt AS FIN,
A.objet_id AS INCREMENT
FROM dbo.APX_HISTO A
LEFT OUTER JOIN dbo.APX_HISTO B
ON (A.objet_id = B.objet_id
AND A.dossier_id = B.dossier_id
AND B.objet_type = 6
AND B.op_type = 3)
WHERE A.objet_type = 6
AND A.op_type = 1


A+

[Yann]

Merci LoveBuff : je suis un peu dégouté de ne pas y avoir pensé, mais l'essentiel est que ça marche !
Merci encore !