Besoin d'aide pour une requête (OUTER APPLY inside)

**Kropernic** · 30/10/2012, 14h21

Bonjour à tous,

Partant d'une requête proposée par iberserk ici pour un problème similaire, je suis arrivé à une requête donnant le résultat escompté mais... pas tout le temps.

Pourriez-vous m'aider à la corriger ?
Ou si c'est plus simple/rapide, m'aider à comprendre le fonctionnement de cet opérateur OUTER APPLY qui, je crois, est ici le nœud du problème.

Si je comprenais parfaitement ce que fais la requête de iberserk, je ne viendrais pas chercher de l'aide évidemment. Le problème est bien là, je ne la comprends que dans les grandes lignes et de ce fait, j'ai joué à l'apprenti sorcier pour parvenir à la requête défectueuse que voici (le DDL des tables concernées ainsi qu'un jeu de test se trouve en fin de message) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
    SELECT
            'CRD' AS 'SUPPORT',
            CRD.TCA_ID,
            MIN(CRD.CRD_SERIAL) AS 'MIN',
            MAX(CRD.CRD_SERIAL) AS 'MAX',
            JGD.JGD_VALUE
    FROM
            DBO.TJ_GFT_DOS_JGD JGD
                INNER JOIN DBO.T_CARD_CRD CRD
                    ON    JGD.GFT_ID = CRD.GFT_ID 
                OUTER APPLY    (
                            SELECT
                                    MIN(CRD_SERIAL) AS 'CRD_SERIAL'
                            FROM
                                    (
                                    SELECT
                                            CRD_SERIAL,
                                            TCA_ID
                                    FROM
                                            dbo.T_CARD_CRD
                                    WHERE
                                            CRD_SERIAL NOT IN    (
                                                                SELECT 
                                                                        CRD_SERIAL 
                                                                FROM 
                                                                        TJ_GFT_DOS_JGD JGD2
                                                                            INNER JOIN T_CARD_CRD CRD3
                                                                                ON    JGD2.GFT_ID = CRD3.GFT_ID 
                                                                WHERE 
                                                                        DOS_ID = @DOS_ID)
                                                                ) CRD2
                            WHERE
                                    CRD.TCA_ID = CRD2.TCA_ID
                                AND    CRD2.CRD_SERIAL > CRD.CRD_SERIAL 
                            ) T
    WHERE
            JGD.DOS_ID = @DOS_ID 
    GROUP BY
            CRD.TCA_ID,
            T.CRD_SERIAL,
            JGD.JGD_VALUE
    ORDER BY
            CRD.TCA_ID,
            [MIN]

Ce qui donne ceci comme résultat (là c'est ok):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SUPPORT    TCA_ID    MIN    MAX    JGD_VALUE
CRD         9         1      2        100.00
CRD         9         4      6        100.00
CRD        11         1      2        200.00

Par contre, si à la table TJ_GFT_DOS_JGD, j'ajoute certaines lignes, les 2 premières du résultat ci-dessous sont compactées en une seul avec MIN = 1 et MAX = 6 ce qui n'est bien sûr pas correct.

Voici les DDL des tables concernées (j'ai mis en commentaire les références vers les tables non concernées par cette requête) et un jeu de données de test.

DDL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
 
CREATE TABLE [dbo].[T_GIFT_GFT](
    [GFT_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_T_GIFT_GFT] PRIMARY KEY CLUSTERED 
(
    [GFT_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
-------------------------
CREATE TABLE [dbo].[T_DOSSIER_DOS](
    [DOS_ID] [int] IDENTITY(1,1) NOT NULL,
    [CLI_ID] [int] NOT NULL,
    [DOS_DATE] [datetime] NOT NULL,
    [DOS_FINALIZED] [bit] NOT NULL,
 CONSTRAINT [PK_T_DOSSIER_DOS] PRIMARY KEY CLUSTERED 
(
    [DOS_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
/*ALTER TABLE [dbo].[T_DOSSIER_DOS]  WITH CHECK ADD  CONSTRAINT [FK_T_DOSSIER_DOS_T_CLIENT_CLI] FOREIGN KEY([CLI_ID])
REFERENCES [dbo].[T_CLIENT_CLI] ([CLI_ID])
GO
 
ALTER TABLE [dbo].[T_DOSSIER_DOS] CHECK CONSTRAINT [FK_T_DOSSIER_DOS_T_CLIENT_CLI]
GO*/
 
ALTER TABLE [dbo].[T_DOSSIER_DOS] ADD  CONSTRAINT [DF_T_DOSSIER_DOS_DOS_FINALIZED]  DEFAULT ((0)) FOR [DOS_FINALIZED]
GO
---------------------------
CREATE TABLE [dbo].[T_CARD_CRD](
    [GFT_ID] [int] NOT NULL,
    [CRD_PREFIX] [tinyint] NOT NULL,
    [TCA_ID] [smallint] NOT NULL,
    [CRD_SERIAL] [int] NOT NULL,
    [CRD_RECHARGEABLE] [bit] NOT NULL,
 CONSTRAINT [PK_T_CARD_CRD] PRIMARY KEY CLUSTERED 
(
    [GFT_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[T_CARD_CRD]  WITH CHECK ADD  CONSTRAINT [FK_T_CARD_CRD_T_GIFT_GFT] FOREIGN KEY([GFT_ID])
REFERENCES [dbo].[T_GIFT_GFT] ([GFT_ID])
GO
 
ALTER TABLE [dbo].[T_CARD_CRD] CHECK CONSTRAINT [FK_T_CARD_CRD_T_GIFT_GFT]
GO
 
/*ALTER TABLE [dbo].[T_CARD_CRD]  WITH CHECK ADD  CONSTRAINT [FK_T_CARD_CRD_T_TYPE_CARD_TCA] FOREIGN KEY([TCA_ID])
REFERENCES [dbo].[T_TYPE_CARD_TCA] ([TCA_ID])
GO
 
ALTER TABLE [dbo].[T_CARD_CRD] CHECK CONSTRAINT [FK_T_CARD_CRD_T_TYPE_CARD_TCA]
GO*/
 
ALTER TABLE [dbo].[T_CARD_CRD] ADD  CONSTRAINT [DF_T_CARD_CRD_CRD_PREFIX]  DEFAULT ((24)) FOR [CRD_PREFIX]
GO
---------------------------
CREATE TABLE [dbo].[TJ_GFT_DOS_JGD](
    [GFT_ID] [int] NOT NULL,
    [DOS_ID] [int] NOT NULL,
    [JGD_VALUE] [decimal](6, 2) NOT NULL,
 CONSTRAINT [PK_TJ_GFT_DOS_JGD] PRIMARY KEY CLUSTERED 
(
    [GFT_ID] ASC,
    [DOS_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[TJ_GFT_DOS_JGD]  WITH CHECK ADD  CONSTRAINT [FK_TJ_GFT_DOS_JGD_T_DOSSIER_DOS] FOREIGN KEY([DOS_ID])
REFERENCES [dbo].[T_DOSSIER_DOS] ([DOS_ID])
ON DELETE CASCADE
GO
 
ALTER TABLE [dbo].[TJ_GFT_DOS_JGD] CHECK CONSTRAINT [FK_TJ_GFT_DOS_JGD_T_DOSSIER_DOS]
GO
 
ALTER TABLE [dbo].[TJ_GFT_DOS_JGD]  WITH CHECK ADD  CONSTRAINT [FK_TJ_GFT_DOS_JGD_T_GIFT_GFT] FOREIGN KEY([GFT_ID])
REFERENCES [dbo].[T_GIFT_GFT] ([GFT_ID])
GO
 
ALTER TABLE [dbo].[TJ_GFT_DOS_JGD] CHECK CONSTRAINT [FK_TJ_GFT_DOS_JGD_T_GIFT_GFT]
GO

Jeu de tests :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
INSERT INTO T_DOSSIER(CLI_ID,DOS_DATE)
VALUES(1,GETDATE());
 
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
INSERT INTO T_GIFT_GFT DEFAULT VALUES;
 
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(1, 24, 11, 1, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(2, 24, 11, 2, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(3, 24, 11, 3, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(4, 24, 12, 1, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(5, 24, 12, 2, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(6, 24, 12, 3, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(7, 24, 9, 1, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(8, 24, 9, 2, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(9, 24, 9, 3, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(10, 24, 9, 4, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(11, 24, 9, 5, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(12, 24, 22, 1, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(13, 24, 9, 6, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(14, 24, 9, 7, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(15, 24, 9, 8, 0);
INSERT INTO T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
VALUES(16, 24, 9, 9, 0);
 
INSERT INTO TJ_GFT_DOS_JGD(GFT_ID, DOS_ID, JGD_VALUE)
VALUES(1, 1, 200.00);
INSERT INTO TJ_GFT_DOS_JGD(GFT_ID, DOS_ID, JGD_VALUE)
VALUES(2, 1, 200.00);
INSERT INTO TJ_GFT_DOS_JGD(GFT_ID, DOS_ID, JGD_VALUE)
VALUES(7, 1, 100.00);
INSERT INTO TJ_GFT_DOS_JGD(GFT_ID, DOS_ID, JGD_VALUE)
VALUES(8, 1, 100.00);
INSERT INTO TJ_GFT_DOS_JGD(GFT_ID, DOS_ID, JGD_VALUE)
VALUES(10, 1, 100.00);
INSERT INTO TJ_GFT_DOS_JGD(GFT_ID, DOS_ID, JGD_VALUE)
VALUES(11, 1, 100.00);
INSERT INTO TJ_GFT_DOS_JGD(GFT_ID, DOS_ID, JGD_VALUE)
VALUES(13, 1, 100.00);

Pour finir, les lignes qui font que le résultat est faussé (notez que si je n'en ajoute que 2 sur les 3, le résultat n'est pas faussé) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
INSERT INTO TJ_GFT_DOS_JGD(GFT_ID, DOS_ID, JGD_VALUE)
VALUES(4, 1, 150.00);
INSERT INTO TJ_GFT_DOS_JGD(GFT_ID, DOS_ID, JGD_VALUE)
VALUES(5, 1, 150.00);
INSERT INTO TJ_GFT_DOS_JGD(GFT_ID, DOS_ID, JGD_VALUE)
VALUES(6, 1, 150.00);

J'espère ne rien avoir oublié... :-/

**Kropernic** · 30/10/2012, 15h11

Je pense avoir réussi à corriger ma requête mais je laisse ouvert car je ne serais vraiment pas contre un brin d'explication.

Besoin d'aide pour une requête (OUTER APPLY inside)

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager