Допоможіть розібратись

[zener]

Доброго дня, в звіті "Реалізація товару"

SELECT
 sw.group_name,  
 tm.tovar_id,  
 tn.kod,  
 tn.name,  
 tn.ed_izm,  
 tn.cena,  
 tn.cena_r,  
 tn.cena_o,  
 SUM(tm.suma_znig_diff) AS tov_znig,  
 SUM(tm.from_kolvo) AS tov_kolvo,  
 AVG(tm.from_cena) AS tov_cena,  
 AVG(tm.cena_pdv) AS tov_cena_pdv,  
 SUM(tm.from_suma) AS tov_suma,  
 SUM(tm.suma_pdv) AS tov_pdv,  
 SUM(tm.from_suma+tm.suma_pdv) AS tov_suma_pdv  
FROM  
 tovar_move tm,  
 vnakl vn,
 print_view_sklad(#товар_группа_№#,#склад_№#) sw,
 tovar_name tn  
WHERE  
 tm.doc_type_id = 1 AND  
 tm.doc_id = vn.num AND
#if #пользователь_№# = -1 #then
#else
 vn.doc_user_id = #пользователь_№# AND
#endif  
 tm.mdate >= '#дата_с#' AND
 tm.mdate <= '#дата_по#' AND
 sw.num = tn.tip AND  
 tm.tovar_id = tn.num AND  
 tm.from_sklad_id = #склад_№#  
GROUP BY  
 1,2,3,4,5,6,7,8  
ORDER BY  
 sw.group_name

хочу добавити додаткові поля товару, і зробити так щоб ціна відображалась тільки в національній валюті...

[zener]

відредагував звіт ось так:

SELECT
 sw.group_name,  
 tz.kolvo,
 t.*,
 CASE
   WHEN (t.cena_curr_id = par.param) THEN t.cena
   WHEN (c.how_to_calc = 0 AND ct_in.how_to_calc = 1) THEN t.cena/c.kurs/ct_in.kurs
   WHEN (c.how_to_calc = 1 AND ct_in.how_to_calc = 1) THEN t.cena*c.kurs/ct_in.kurs  
   WHEN (c.how_to_calc = 0 AND ct_in.how_to_calc = 0) THEN t.cena/c.kurs*ct_in.kurs
   WHEN (c.how_to_calc = 1 AND ct_in.how_to_calc = 0) THEN t.cena*c.kurs*ct_in.kurs  
 END AS cena_national_valuta,
 CASE
   WHEN (t.cena_out_curr_id = par.param) THEN t.cena_r
   WHEN (c.how_to_calc = 0 AND ct_out.how_to_calc = 1) THEN t.cena_r/c.kurs/ct_out.kurs
   WHEN (c.how_to_calc = 1 AND ct_out.how_to_calc = 1) THEN t.cena_r*c.kurs/ct_out.kurs
   WHEN (c.how_to_calc = 0 AND ct_out.how_to_calc = 0) THEN t.cena_r/c.kurs*ct_out.kurs
   WHEN (c.how_to_calc = 1 AND ct_out.how_to_calc = 0) THEN t.cena_r*c.kurs*ct_out.kurs
 END AS cena_r_national_valuta,
 CASE
   WHEN (t.cena_out_curr_id = par.param) THEN t.cena_o
   WHEN (c.how_to_calc = 0 AND ct_out.how_to_calc = 1) THEN t.cena_o/c.kurs/ct_out.kurs
   WHEN (c.how_to_calc = 1 AND ct_out.how_to_calc = 1) THEN t.cena_o*c.kurs/ct_out.kurs  
   WHEN (c.how_to_calc = 0 AND ct_out.how_to_calc = 0) THEN t.cena_o/c.kurs*ct_out.kurs
   WHEN (c.how_to_calc = 1 AND ct_out.how_to_calc = 0) THEN t.cena_o*c.kurs*ct_out.kurs  
 END AS cena_o_national_valuta,
 CASE
   WHEN (t.cena_out_curr_id = par.param) THEN t.cena_1
   WHEN (c.how_to_calc = 0 AND ct_out.how_to_calc = 1) THEN t.cena_1/c.kurs/ct_out.kurs
   WHEN (c.how_to_calc = 1 AND ct_out.how_to_calc = 1) THEN t.cena_1*c.kurs/ct_out.kurs  
   WHEN (c.how_to_calc = 0 AND ct_out.how_to_calc = 0) THEN t.cena_1/c.kurs*ct_out.kurs
   WHEN (c.how_to_calc = 1 AND ct_out.how_to_calc = 0) THEN t.cena_1*c.kurs*ct_out.kurs  
 END AS cena_1_national_valuta,
 CASE
   WHEN (t.cena_out_curr_id = par.param) THEN t.cena_2
   WHEN (c.how_to_calc = 0 AND ct_out.how_to_calc = 1) THEN t.cena_2/c.kurs/ct_out.kurs
   WHEN (c.how_to_calc = 1 AND ct_out.how_to_calc = 1) THEN t.cena_2*c.kurs/ct_out.kurs
   WHEN (c.how_to_calc = 0 AND ct_out.how_to_calc = 0) THEN t.cena_2/c.kurs*ct_out.kurs
   WHEN (c.how_to_calc = 1 AND ct_out.how_to_calc = 0) THEN t.cena_2*c.kurs*ct_out.kurs
 END AS cena_2_national_valuta,
 CASE
   WHEN (t.cena_out_curr_id = par.param) THEN t.cena_3
   WHEN (c.how_to_calc = 0 AND ct_out.how_to_calc = 1) THEN t.cena_3/c.kurs/ct_out.kurs
   WHEN (c.how_to_calc = 1 AND ct_out.how_to_calc = 1) THEN t.cena_3*c.kurs/ct_out.kurs
   WHEN (c.how_to_calc = 0 AND ct_out.how_to_calc = 0) THEN t.cena_3/c.kurs*ct_out.kurs
   WHEN (c.how_to_calc = 1 AND ct_out.how_to_calc = 0) THEN t.cena_3*c.kurs*ct_out.kurs
 END AS cena_3_national_valuta,
 CASE    
   WHEN (t.cena_curr_id = 0) THEN t.cena
   WHEN (ct_in.how_to_calc = 1) THEN t.cena/ct_in.kurs
   WHEN (ct_in.how_to_calc = 0) THEN t.cena*ct_in.kurs
 END AS cena_main_valuta,
 CASE    
   WHEN (t.cena_curr_id = 0) THEN t.cena_r
   WHEN (ct_out.how_to_calc = 1) THEN t.cena_r/ct_out.kurs
   WHEN (ct_out.how_to_calc = 0) THEN t.cena_r*ct_out.kurs
 END AS cena_r_main_valuta,
 CASE    
   WHEN (t.cena_curr_id = 0) THEN t.cena_o
   WHEN (ct_out.how_to_calc = 1) THEN t.cena_o/ct_out.kurs
   WHEN (ct_out.how_to_calc = 0) THEN t.cena_o*ct_out.kurs
 END AS cena_o_main_valuta,
 CASE    
   WHEN (t.cena_curr_id = 0) THEN t.cena_1
   WHEN (ct_out.how_to_calc = 1) THEN t.cena_1/ct_out.kurs
   WHEN (ct_out.how_to_calc = 0) THEN t.cena_1*ct_out.kurs
 END AS cena_1_main_valuta,
 CASE    
   WHEN (t.cena_curr_id = 0) THEN t.cena_2
   WHEN (ct_out.how_to_calc = 1) THEN t.cena_2/ct_out.kurs
   WHEN (ct_out.how_to_calc = 0) THEN t.cena_2*ct_out.kurs
 END AS cena_2_main_valuta,
 CASE    
   WHEN (t.cena_curr_id = 0) THEN t.cena_3
   WHEN (ct_out.how_to_calc = 1) THEN t.cena_3/ct_out.kurs
   WHEN (ct_out.how_to_calc = 0) THEN t.cena_3*ct_out.kurs
 END AS cena_3_main_valuta,
 gr.group_name,
 cln.fio,
 vn.date_dok,  
 vn.nu,  
 vn_.tov_name,  
 vn_.tov_ed,  
 vn_.tov_cena,  
 vn_.tov_kolvo,  
 vn_.tov_suma,  
 vn.sklad_id,  
 vn.cena,  
 vn.cena_znig,
 vn.znig_type,
 vn.num,
 tm.tovar_id,  
 tn.kod,  
 tn.name,  
 tn.ed_izm,  
 tn.cena,  
 tn.cena_r,  
 tn.cena_o,  
 SUM(tm.suma_znig_diff) AS tov_znig,  
 SUM(tm.from_kolvo) AS tov_kolvo,  
 AVG(tm.from_cena) AS tov_cena,  
 AVG(tm.cena_pdv) AS tov_cena_pdv,  
 SUM(tm.from_suma) AS tov_suma,  
 SUM(tm.suma_pdv) AS tov_pdv,  
 SUM(tm.from_suma+tm.suma_pdv) AS tov_suma_pdv  
FROM  
 tovar_move tm,  
 vnakl vn,
 print_view_sklad(#товар_группа_№#,#склад_№#) sw,
 tovar_name tn,
tovar_name t
   LEFT JOIN currency ct_in ON t.cena_curr_id = ct_in.num
   LEFT JOIN currency ct_out ON t.cena_out_curr_id = ct_out.num,
 print_view_sklad(#товар_группа_№#,#склад_№#) gr,
 tovar_zal tz,  
 currency c,  
 params par,
 vnakl vn,
 client cln,  
 vnakl_ vn_
WHERE  
 vn.is_move = 1 AND
 vn.date_dok >= '#дата_с#' AND
 vn.date_dok <= '#дата_по#' AND
 vn.client_id = cln.num AND  
 vn.num = vn_.pid AND
 vn_.sklad_id = #склад_№#    AND  
 par.name = 'NationalValutaId' AND  
 c.num = par.param AND  
 t.tip = gr.num AND  
 vn_.tovar_id = t.num AND
 t.visible = 1 AND  
 t.is_price_invisible = 0 AND
 t.num=tz.tovar_id AND  
 tz.sklad_id=#склад_№# AND
 tm.doc_type_id = 1 AND  
 tm.doc_id = vn.num AND
#if #пользователь_№# = -1 #then
#else
 vn.doc_user_id = #пользователь_№# AND
#endif  
 tm.mdate >= '#дата_с#' AND
 tm.mdate <= '#дата_по#' AND
 sw.num = tn.tip AND  
 tm.tovar_id = tn.num AND  
 tm.from_sklad_id = #склад_№#  
GROUP BY  
 1,2,3,4,5,6,7,8  
ORDER BY  
 vn.client,  
 vn.date_dok,  
 vn.num,
 gr.group_name,  
 t.name,
 sw.group_name



викидає помилку
DataM.QueryRep.SelectQuery:
Undefined name.
Dynamic SQL Error.
SQL error code =-204
Alias VN conflict with an alias in the same statement