Discussion :

[medhan]

Bonjour

Problème : Mise à jour d'un champ de table à partir le résultat d'une requête faite par [ With et CROSS]

Je possède une table Checks

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE dbo.Checks ( Client VARCHAR(32), RowNr int, Amount DECIMAL(12,2), Cumul DECIMAL(12,2), );
 
INSERT dbo.Checks(Client, RowNr, Amount)
          SELECT 'Company A', '1', 1
UNION ALL SELECT 'Company A', '2', 1
UNION ALL SELECT 'Company A', '3', 5
UNION ALL SELECT 'Company B', '4', 3
UNION ALL SELECT 'Company A', '5', 1
UNION ALL SELECT 'Company B', '6', 2
UNION ALL SELECT 'Company B', '7', 1
UNION ALL SELECT 'Company B', '8', 5;

Je dois calculer le cumul de la colonne Amount c'est pourquoi j'ai procédé à ceci :

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
WITH Islands AS
(
  SELECT Client, RowNr, Amount, RowNr-ROW_NUMBER()OVER(PARTITION BY Client ORDER BY RowNr) IslId
  FROM Checks
)
 
SELECT *
FROM Islands I1
CROSS APPLY
(
  SELECT SUM(Amount) Cumul
  FROM Islands I2
  WHERE I1.Client = I2.Client
  AND I1.IslId = I2.IslId
  AND I1.RowNr >= I2.RowNr
)C

La requête dessus me calcul très bien le "Cumul" du champ "Amount"

Maintenant je veux bien mettre à jour le champ Cumul par la jointure checks.RowNr = Islands.RowNr mais je n'arrive pas à faire..! Qu'est ce qu'il y a comme problème sur le code dessous ?

Merci 1000 fois

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
Update checks set checks.cumul = Islands.cumul from
WITH Islands AS
(
  SELECT Client, RowNr, Amount, RowNr-ROW_NUMBER()OVER(PARTITION BY Client ORDER BY RowNr) IslId
  FROM Checks
)
 
SELECT *
FROM Islands I1
CROSS APPLY
(
  SELECT SUM(Amount) Cumul
  FROM Islands I2
  WHERE I1.Client = I2.Client
  AND I1.IslId = I2.IslId
  AND I1.RowNr >= I2.RowNr
)C
 
join checks on
checks.RowNr  = Islands.RowNr

[al1_24]

Peut-être comme cela ? (je n'ai pas les moyens de tester)

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
WITH    Islands 
AS  (   SELECT  Client
            ,   RowNr
            ,   Amount
            ,   RowNr - ROW_NUMBER() OVER(PARTITION BY Client ORDER BY RowNr) IslId
        FROM    Checks
    )
    ,  Cumul
AS  (   SELECT  *    
        FROM    Islands I1
        CROSS APPLY
            (   SELECT  SUM(Amount) Cumul
                FROM    Islands I2
                WHERE   I1.Client = I2.Client
                    AND I1.IslId = I2.IslId
                    AND I1.RowNr >= I2.RowNr
            )   C
    )
UPDATE  checks 
SET     checks.cumul = Cumul.cumul 
FROM    checks
    JOIN 
        Cumul
        ON  checks.RowNr = Islands.RowNr

[aieeeuuuuu]

Bonjour,

La clause WITH marquant la CTE doit obligatoirement débuter la requête.

Dans votre cas, puisque vous avez une requete qui fonctionne, vous pouvez la mettre également en CTE, et mettre à jour celle-ci. Il suffit pour cela d'ajouter la colonne cumul à votre CTE (car elle est la cible de la mise à jour)

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
 
WITH Islands AS
(
  SELECT Client, RowNr, Amount,cumul, RowNr-ROW_NUMBER()OVER(PARTITION BY Client ORDER BY RowNr) IslId
  FROM Checks
), 
tmp as (
	SELECT I1.cumul as C1, C.cumul as C2
	FROM Islands I1
	CROSS APPLY
	(
	  SELECT SUM(Amount) Cumul
	  FROM Islands I2
	  WHERE I1.Client = I2.Client
	  AND I1.IslId = I2.IslId
	  AND I1.RowNr >= I2.RowNr
	)C
)
update tmp set C1 = C2

Sans passer par une deuxième CTE (donc tel que vous essayiez de le faire), ce serait comme ceci :

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
 
WITH Islands AS
(
  SELECT Client, RowNr, Amount,cumul, RowNr-ROW_NUMBER()OVER(PARTITION BY Client ORDER BY RowNr) IslId
  FROM Checks
)
UPDATE I1
	SET cumul = C.cumul 
FROM Islands I1
CROSS APPLY
	(
	  SELECT SUM(Amount) Cumul
	  FROM Islands I2
	  WHERE I1.Client = I2.Client
	  AND I1.IslId = I2.IslId
	  AND I1.RowNr >= I2.RowNr
	)C
;

Attention toutefois au CROSS APPLY qui peut être couteux sur des fortes volumétries.
Vous pouvez avantagement le remplacement par une jointure sur une sous requete en table dérivée :

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
 
WITH Islands AS
(
  SELECT Client, RowNr, Amount,cumul, RowNr-ROW_NUMBER()OVER(PARTITION BY Client ORDER BY RowNr) IslId
  FROM Checks
)
UPDATE I1
	set Cumul = I2.cumul 
FROM Islands I1
INNER JOIN
(
  SELECT Client, IslId,RowNr ,SUM(Amount) Cumul
  FROM Islands 
  GROUP BY Client, IslId,RowNr
)I2
	ON I1.Client = I2.Client
	  AND I1.IslId = I2.IslId
	  AND I1.RowNr >= I2.RowNr

[Lyche]

Bonjour,

Je ne sais pas quelle version de SQL Serveur tu as. Cependant, il est possible depuis 2012 de faire des CTE sur des VALUES. Je m'explique.

Code sql :

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
 
WITH ListInsert AS (
SELECT *
      , RowNr-ROW_NUMBER()OVER(PARTITION BY Client ORDER BY RowNr) IslId
  FROM ( VALUES ( 'Company A', '1', 1 )
              , ( 'Company A', '2', 1 )
              , ( 'Company A', '3', 5 )
              , ( 'Company B', '4', 3 )
              , ( 'Company A', '5', 1 )
              , ( 'Company B', '6', 2 )
              , ( 'Company B', '7', 1 )
              , ( 'Company B', '8', 5 ) ) AS List (Client, RowNr, Amount) )
 
SELECT Client, RowNr, Amount, I1.IslId
 FROM ListInsert I1
 CROSS APPLY
(
  SELECT SUM(Amount) Cumul
  FROM ListInsert I2
  WHERE I1.Client = I2.Client
  AND I1.IslId = I2.IslId
  AND I1.RowNr >= I2.RowNr
)C

Je suis peut-être HS, mais je trouve cette forme plus élégante et surtout elle limite le nombre d'actions sur la table (un seul insert, pas d'update). Je conçoit que sur un si petit volume ce ne soit pas important, mais je suis maniaque

Cordialement,