Discussion :

[jmclej]

Bonjour,
Est-ce que vous pouvez me confirmer que ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
update CurrencyPair
set Rate = (1/err1.Rate) * err2.Rate
from ExchangeRateReadable err1, ExchangeRateReadable err2, CurrencyPair cp
where err1.Month = cp.Month
  and err1.Year = cp.Year
  and err1.BaseCcy = 'GBP'
  and err1.QuotedCcy = cp.Ccy1
  and err1.Rate is not null
  and err1.Rate != 0
  and err2.Month = cp.Month
  and err2.Year = cp.Year
  and err2.BaseCcy = 'GBP'
  and err2.QuotedCcy = cp.Ccy2

équivaut à:

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
declare @ccy1 int, @ccy2 int, @month int, @year int
declare curs2 cursor
for select cp.Ccy1, cp.Ccy2, cp.month, cp.year
	from CurrencyPair cp
open curs2
fetch from curs2 into @ccy1, @ccy2, @month, @year
while @@fetch_status = 0
begin
	update CurrencyPair
	set Rate = (1/err1.Rate) * err2.Rate
	from ExchangeRateReadable err1, ExchangeRateReadable err2
	where err1.Month = @month
	  and err1.Year = @year
	  and err1.BaseCcy = 'GBP'
	  and err1.QuotedCcy = @ccy1
	  and err1.Rate is not null
	  and err1.Rate != 0
	  and err2.Month = @month
	  and err2.Year = @year
	  and err2.BaseCcy = 'GBP'
	  and err2.QuotedCcy = @ccy2
 
	fetch from curs2 into @ccy1, @ccy2, @month, @year
end
close curs2
deallocate curs2

[jmclej]

Y aurait-il une âme charitable pour me confirmer cela s'il vous plaît?

[Waldar]

Oui ça m'a l'air correct.
J'aurai légèrement revu la syntaxe et écrit ainsi, mais ça ne change rien à la finalité :

Code :

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

1
2
3
4
5
6
7
8
UPDATE cp
   SET cp.Rate = (1 / err1.Rate) * err2.Rate
  FROM CurrencyPair         as cp
  JOIN ExchangeRateReadable as err1 ON err1.Month = cp.Month AND err1.Year = cp.Year AND err1.QuotedCcy = cp.Ccy1
  JOIN ExchangeRateReadable as err2 ON err2.Month = cp.Month AND err2.Year = cp.Year AND err2.QuotedCcy = cp.Ccy2
 WHERE err1.BaseCcy = 'GBP'
   AND err1.Rate <> 0
   AND err2.BaseCcy = 'GBP';