Update avec Case

Version imprimable

Update avec Case

Bonjour a tous,

Je tente de faire l'update suivant

Code:

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
update tblImportKE30_IK3
set
	tblImportKE30_IK3.IK3_Etat='KE30',
	tblImportKE30_IK3.EXR_ExchangeRate = EXR.EXR_ExchangeRate,
	tblImportKE30_IK3.IK3_Month = right(tblImportKE30_IK3.IK3_YearMonth,2),
	tblImportKE30_IK3.IK3_Trim = MOC.MOC_Quarter,
	tblImportKE30_IK3.IK3_COGSEuros = tblImportKE30_IK3.IK3_SalesEuros - tblImportKE30_IK3.IK3_MarginEuros,
	case when tblImportKE30_IK3.IK3_Qty > 0 then
	tblImportKE30_IK3.IK3_PVUEuros = tblImportKE30_IK3.IK3_SalesEuros / tblImportKE30_IK3.IK3_Qty,
	tblImportKE30_IK3.IK3_PVUUSD = (tblImportKE30_IK3.IK3_SalesEuros * EXR.EXR_ExchangeRate) / tblImportKE30_IK3.IK3_Qty,
	tblImportKE30_IK3.IK3_CRUEuros = (tblImportKE30_IK3.IK3_SalesEuros - tblImportKE30_IK3.IK3_MarginEuros) / tblImportKE30_IK3.IK3_Qty,
	tblImportKE30_IK3.IK3_CRUUSD = (tblImportKE30_IK3.IK3_SalesEuros * EXR.EXR_ExchangeRate - tblImportKE30_IK3.IK3_MarginEuros * EXR.EXR_ExchangeRate) / tblImportKE30_IK3.IK3_Qty,
	end
	tblImportKE30_IK3.IK3_SalesUSD = tblImportKE30_IK3.IK3_SalesEuros * EXR.EXR_ExchangeRate,
	tblImportKE30_IK3.IK3_MarginUSD =  tblImportKE30_IK3.IK3_MarginEuros * EXR.EXR_ExchangeRate,
	tblImportKE30_IK3.IK3_COGSUSD = tblImportKE30_IK3.IK3_SalesEuros * EXR.EXR_ExchangeRate - tblImportKE30_IK3.IK3_MarginEuros * EXR.EXR_ExchangeRate,
	tblimportKE30_IK3.IK3_Closed = 0
 
from tblImportKE30_IK3
-- Jointure pour le calcul du taux $
	left outer join tblExchangeRate_EXR EXR
		on EXR.EXR_MonthCode=tblImportKE30_IK3.IK3_YearMonth
		and EXR_CurrencyCode='USD'
 
-- Jointure pour calcul Trimestre
	left outer join tblMonthCoding_MOC MOC
		on MOC.MOC_MonthId=CAST(RIGHT(tblImportKE30_IK3.IK3_YearMonth,2) as SMALLINT)
 
where ((IK3_Closed =0 or IK3_Closed is null) and (IK3_Manuel = 0 or IK3_Manuel is null));
GO

Mais j'obtiens : Syntaxe incorrecte vers le mot clé 'case'.

Ou est l'erreur

Merci pour votre aide

Tornade

Case generalise

Citation:

Case Généralisé:
CASE WHEN expression THEN Valeur END

Code:

1
2
3
4
5
6
7
case when tblImportKE30_IK3.IK3_Qty > 0 then
    tblImportKE30_IK3.IK3_PVUEuros = tblImportKE30_IK3.IK3_SalesEuros / tblImportKE30_IK3.IK3_Qty,
    tblImportKE30_IK3.IK3_PVUUSD = (tblImportKE30_IK3.IK3_SalesEuros * EXR.EXR_ExchangeRate) / tblImportKE30_IK3.IK3_Qty,
    tblImportKE30_IK3.IK3_CRUEuros = (tblImportKE30_IK3.IK3_SalesEuros - tblImportKE30_IK3.IK3_MarginEuros) / tblImportKE30_IK3.IK3_Qty,
    tblImportKE30_IK3.IK3_CRUUSD = (tblImportKE30_IK3.IK3_SalesEuros * EXR.EXR_ExchangeRate - tblImportKE30_IK3.IK3_MarginEuros * EXR.EXR_ExchangeRate) / tblImportKE30_IK3.IK3_Qty,
    end

devient

Code:

1
2
3
4
5
6
7
8
9
tblImportKE30_IK3.IK3_PVUEuros = case when tblImportKE30_IK3.IK3_Qty > 0 then
 tblImportKE30_IK3.IK3_SalesEuros / tblImportKE30_IK3.IK3_Qty else null end,
tblImportKE30_IK3.IK3_PVUUSD = case when tblImportKE30_IK3.IK3_Qty > 0 then
(tblImportKE30_IK3.IK3_SalesEuros * EXR.EXR_ExchangeRate) / tblImportKE30_IK3.IK3_Qty else null end,
tblImportKE30_IK3.IK3_CRUEuros = case when tblImportKE30_IK3.IK3_Qty > 0 then
 (tblImportKE30_IK3.IK3_SalesEuros - tblImportKE30_IK3.IK3_MarginEuros) / tblImportKE30_IK3.IK3_Qty else null end,
tblImportKE30_IK3.IK3_CRUUSD = case when tblImportKE30_IK3.IK3_Qty > 0 then
 (tblImportKE30_IK3.IK3_SalesEuros * EXR.EXR_ExchangeRate - tblImportKE30_IK3.IK3_MarginEuros * EXR.EXR_ExchangeRate) / tblImportKE30_IK3.IK3_Qty else null end,

Merci pour ton aide, mais j'ai modifier :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
update tblImportKE30_IK3
set
	tblImportKE30_IK3.IK3_Etat='KE30',
	tblImportKE30_IK3.EXR_ExchangeRate = EXR.EXR_ExchangeRate,
	tblImportKE30_IK3.IK3_Month = right(tblImportKE30_IK3.IK3_YearMonth,2),
	tblImportKE30_IK3.IK3_Trim = MOC.MOC_Quarter,
	tblImportKE30_IK3.IK3_COGSEuros = tblImportKE30_IK3.IK3_SalesEuros - tblImportKE30_IK3.IK3_MarginEuros,
	case when tblImportKE30_IK3.IK3_Qty > 0 then
	tblImportKE30_IK3.IK3_PVUEuros = tblImportKE30_IK3.IK3_SalesEuros / tblImportKE30_IK3.IK3_Qty,
	end
	case when tblImportKE30_IK3.IK3_Qty > 0 then
	tblImportKE30_IK3.IK3_PVUUSD = (tblImportKE30_IK3.IK3_SalesEuros * EXR.EXR_ExchangeRate) / tblImportKE30_IK3.IK3_Qty,
	end
	case when tblImportKE30_IK3.IK3_Qty > 0 then
	tblImportKE30_IK3.IK3_CRUEuros = (tblImportKE30_IK3.IK3_SalesEuros - tblImportKE30_IK3.IK3_MarginEuros) / tblImportKE30_IK3.IK3_Qty,
	end
	case when tblImportKE30_IK3.IK3_Qty > 0 then
	tblImportKE30_IK3.IK3_CRUUSD = (tblImportKE30_IK3.IK3_SalesEuros * EXR.EXR_ExchangeRate - tblImportKE30_IK3.IK3_MarginEuros * EXR.EXR_ExchangeRate) / tblImportKE30_IK3.IK3_Qty,
	end
	tblImportKE30_IK3.IK3_SalesUSD = tblImportKE30_IK3.IK3_SalesEuros * EXR.EXR_ExchangeRate,
	tblImportKE30_IK3.IK3_MarginUSD =  tblImportKE30_IK3.IK3_MarginEuros * EXR.EXR_ExchangeRate,
	tblImportKE30_IK3.IK3_COGSUSD = tblImportKE30_IK3.IK3_SalesEuros * EXR.EXR_ExchangeRate - tblImportKE30_IK3.IK3_MarginEuros * EXR.EXR_ExchangeRate,
	tblimportKE30_IK3.IK3_Closed = 0
..........

Avec ou sans les END j'obtiens toujours un message d'erreur

Merci

essai ca

Code:

1
2
tblImportKE30_IK3.IK3_CRUUSD = case when tblImportKE30_IK3.IK3_Qty > 0 then (tblImportKE30_IK3.IK3_SalesEuros * EXR.EXR_ExchangeRate - tblImportKE30_IK3.IK3_MarginEuros * EXR.EXR_ExchangeRate) / tblImportKE30_IK3.IK3_Qty else null end,

j'ai verifie, ca marche!

21/01/2008, 01h15
tornade69

Merci