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
93
94
95
96
97
|
begin
declare
@ods_CUSTOMER_KEY varchar(60),
@ods_CUSTOMER_NAME varchar(100),
@ods_INDUSTRY_LEVEL1_KEY varchar(60),
@ods_INDUSTRY_LEVEL1_NAME varchar(100),
@ods_CUSTOMER_KEY1 varchar(60),
@ods_CUSTOMER_NAME1 varchar(100),
@ods_INDUSTRY_LEVEL1_KEY1 varchar(60),
@ods_INDUSTRY_LEVEL1_NAME1 varchar(100)
--- declaration du curseur MyCursor1
DECLARE MyCursor1 cursor
FOR
select distinct ods_Customer_key, ods_customer_name, ods_industry_level1_key, ods_industry_level1_name
from ods_TMP.dbo.ods_FF_SALES_ITEMS_UK where
substring(CONVERT(VARCHAR(10),ods_load_date, 102), 1, 10) in
(select substring(CONVERT(VARCHAR(10),max(ods_load_date), 102), 1, 10)
from ods_tmp.dbo.ods_ff_sales_items_UK)
and ods_customer_key <> '0'
-- ouverture du curseur
OPEN MyCursor1
-- initialisation des variables
SET @ods_CUSTOMER_KEY =0
SET @ods_CUSTOMER_NAME =0
SET @ods_INDUSTRY_LEVEL1_KEY =0
SET @ods_INDUSTRY_LEVEL1_NAME =0
SET @ods_CUSTOMER_KEY1 =0
SET @ods_CUSTOMER_NAME1 =0
SET @ods_INDUSTRY_LEVEL1_KEY1 =0
SET @ods_INDUSTRY_LEVEL1_NAME1 =0
-- lecture du premier enregistrement
FETCH MyCursor1 into @ods_CUSTOMER_KEY,@ods_CUSTOMER_NAME,@ods_INDUSTRY_LEVEL1_KEY,
@ods_INDUSTRY_LEVEL1_NAME
While @@fetch_status= 0
Begin
--- declaration du curseur MyCursor2
DECLARE MyCursor2 cursor
FOR
select distinct ods_CUSTOMER_KEY, ods_CUSTOMER_NAME, ods_INDUSTRY_LEVEL1_KEY,ods_INDUSTRY_LEVEL1_NAME
from ods_tmp.dbo.ods_FF_SALES_ITEMS_UK
where substring(CONVERT(VARCHAR(10),ods_load_date, 102), 1, 10) not in
(select substring(CONVERT(VARCHAR(10),max(ods_load_date), 102), 1, 10)
from ods_tmp.dbo.ods_ff_sales_items_UK)
and ods_customer_key <> '0'
-- ouverture du curseur
OPEN MyCursor2
-- lecture du premier enregistrement
FETCH MyCursor2 into @ods_CUSTOMER_KEY1,@ods_CUSTOMER_NAME1,@ods_INDUSTRY_LEVEL1_KEY1,
@ods_INDUSTRY_LEVEL1_NAME1
While @@fetch_status= 0
Begin
-- traitement
Print 'CustomerCode=' + CAST(@ods_Customer_key AS VARCHAR(255))
print 'CustomerCode1=' + CAST(@ods_Customer_key1 AS VARCHAR(255))
IF @ods_customer_key = @ods_customer_key1 and @ods_customer_name = @ods_customer_name1
Begin
if @ods_industry_level1_key <> @ods_industry_level1_key1
begin
update ods_tmp.dbo.ods_ff_sales_items_UK set
ods_industry_level1_key = @ods_industry_level1_key1,
ods_industry_level1_name = @ods_industry_level1_name1
where ods_customer_key = @ods_customer_key and
substring(CONVERT(VARCHAR(10),ods_load_date, 102), 1, 10)
in
(select substring(CONVERT(VARCHAR(10),max(ods_load_date), 102), 1, 10)
from ods_tmp.dbo.ods_ff_sales_items_UK)
end
end
-- lecture de l'enregistrement suivant
FETCH MyCursor2 into @ods_CUSTOMER_KEY1,@ods_CUSTOMER_NAME1,@ods_INDUSTRY_LEVEL1_KEY1,
@ods_INDUSTRY_LEVEL1_NAME1
end
-- fermeture du curseur MyCursor2
CLOSE MyCursor2
-- liberation de la mémoire MyCursor2
DEALLOCATE MyCursor2
-- lecture de l'enregistrement suivant
FETCH MyCursor1 into @ods_CUSTOMER_KEY,@ods_CUSTOMER_NAME,@ods_INDUSTRY_LEVEL1_KEY,
@ods_INDUSTRY_LEVEL1_NAME
END
-- fermeture du curseur MyCursor1
CLOSE MyCursor1
-- liberation de la mémoire MyCursor1
DEALLOCATE MyCursor1
END |
