Bonjour,

Je travaille sur une requête pour aggréger des données de 3 tables différentes avec une structure identique (sur SQL Server 2000). La requete que j'utilise sélectionne les données parmi une union de 3 select sur les 3 tables précédemment citées.
La voici :
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
  SELECT '200707', 'P', charge_code,SUM(charge/1.15) as charge, SUM(bonus) as bonus, SUM(duration) as duration,SUM(calls) calls 
    FROM ( 
      SELECT charge_code, SUM(CHANGE_AMOUNT_1) as charge, 
             SUM(CHANGE_AMOUNT_2*0.12/1.15+CHANGE_AMOUNT_3/1.15+change_amount_4*1.99/1.15+change_amount_5*0.12/1.15+change_amount_6/1024/1024*2.5/1.15+change_amount_7*1.99/1.15+change_amount_8*0.09/60/1.15+change_amount_9*0.12/1.15) as bonus,
             SUM(USAGE_AMOUNT) as duration,count(*) calls 
        FROM dbo.CALL_HISTORY_HIST_200707 t 
       WHERE t.COS_ID IN (SELECT COS FROM COS_LINK  WHERE (product_type = 'PREP') AND (PRODUCT_LINE = 'Mobile')) 
    GROUP BY charge_code
   UNION ALL 
      SELECT charge_code, SUM(CHANGE_AMOUNT_1) as charge, 
             SUM(CHANGE_AMOUNT_2*0.12/1.15+CHANGE_AMOUNT_3/1.15+change_amount_4*1.99/1.15+change_amount_5*0.12/1.15+change_amount_6/1024/1024*2.5/1.15+change_amount_7*1.99/1.15+change_amount_8*0.09/60/1.15+change_amount_9*0.12/1.15) as bonus, 
             SUM(USAGE_AMOUNT) as duration,count(*) calls 
        FROM dbo.OSA_HISTORY_HIST_200707 t
       WHERE t.COS_ID IN (SELECT COS FROM COS_LINK  WHERE (product_type = 'PREP') AND (PRODUCT_LINE = 'Mobile')) 
    GROUP BY charge_code
   UNION ALL 
      SELECT charge_code, SUM(CHANGE_AMOUNT_1) as charge, 
             SUM(CHANGE_AMOUNT_2*0.12/1.15+CHANGE_AMOUNT_3/1.15+change_amount_4*1.99/1.15+change_amount_5*0.12/1.15+change_amount_6/1024/1024*2.5/1.15+change_amount_7*1.99/1.15+change_amount_8*0.09/60/1.15+change_amount_9*0.12/1.15) as bonus, 
             SUM(USAGE_AMOUNT) as duration,count(*) calls 
        FROM dbo.PS_TRANSACTION_HIST_200707 t
       WHERE t.COS_ID IN (SELECT COS FROM COS_LINK  WHERE (product_type = 'PREP') AND (PRODUCT_LINE = 'Mobile')) 
    GROUP BY charge_code) as DATA
GROUP BY charge_code
J'ai maintenant besoin de rajouter une information dans chaque sous requête, il s'agit d'un bête entier. Ce qui donnerait (pour une seule sous requête), ceci :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
SELECT 1 as service_code, charge_code, SUM(CHANGE_AMOUNT_1) as charge, 
             SUM(CHANGE_AMOUNT_2*0.12/1.15+CHANGE_AMOUNT_3/1.15+change_amount_4*1.99/1.15+change_amount_5*0.12/1.15+change_amount_6/1024/1024*2.5/1.15+change_amount_7*1.99/1.15+change_amount_8*0.09/60/1.15+change_amount_9*0.12/1.15) as bonus,
             SUM(USAGE_AMOUNT) as duration,count(*) calls 
        FROM dbo.CALL_HISTORY_HIST_200707 t 
       WHERE t.COS_ID IN (SELECT COS FROM COS_LINK  WHERE (product_type = 'PREP') AND (PRODUCT_LINE = 'Mobile')) 
    GROUP BY charge_code, service_code
Problème : SQL Server me renvoie ce message :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
Msg 207, Level 16, State 3, Line 1
Invalid column name 'service_code'.
Je sais comment faire dans le cas où on utilise des CASE comme expression GROUP BY, mais pour les constantes, je n'ai rien trouvé.

Quelqu'un aurait il une idée ?
Merci à tous.