Доброго дня, в звіті "Реалізація товару"
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
хочу добавити додаткові поля товару, і зробити так щоб ціна відображалась тільки в національній валюті...
відредагував звіт ось так:
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