Друкувати сторінку - Вибірка товарів для прайсу за полем ДП

Назва: Вибірка товарів для прайсу за полем ДП
Відправлено: mytskaniuk від Лютий 18, 2013, 17:09:07

Доброго дня,

підкажіть, будь ласка, як модифікувати SQL-запит звіту "Прайс", щоб у нього підтягувалися тільки ті товари, в яких поле ДП1 непорожнє (чи навпаки, порожнє)?

заздалегідь вдячний

Назва: Re: Вибірка товарів для прайсу за полем ДП
Відправлено: TheOK від Лютий 18, 2013, 17:41:54

Протестируйте, я не проверял.С пустым полем дополнительно 1

Код Виділити


SELECT
  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,
  ti.tov_image,
  ti.tov_image_type
FROM
  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
    LEFT JOIN tovar_images ti ON (ti.num = (SELECT FIRST 1 ti2.num FROM tovar_images ti2 WHERE ti2.tovar_id = t.num ORDER BY ti2.isort )),
  print_view_sklad(#товар_группа_№#,#склад_№#) gr,
  tovar_zal tz,
  currency c,
  params par
WHERE
  par.name = 'NationalValutaId' AND
  c.num = par.param AND
  t.tip = gr.num AND
  t.visible = 1 AND
  t.is_price_invisible = 0 AND
  t.num=tz.tovar_id AND
  tz.sklad_id=#склад_№#
  and (t.dopoln1 ='' or t.dopoln1 is null )
ORDER BY
  gr.group_name,
  t.name

с не пустым полем дополнительно 1 :

Код Виділити


SELECT
  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,
  ti.tov_image,
  ti.tov_image_type
FROM
  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
    LEFT JOIN tovar_images ti ON (ti.num = (SELECT FIRST 1 ti2.num FROM tovar_images ti2 WHERE ti2.tovar_id = t.num ORDER BY ti2.isort )),
  print_view_sklad(#товар_группа_№#,#склад_№#) gr,
  tovar_zal tz,
  currency c,
  params par
WHERE
  par.name = 'NationalValutaId' AND
  c.num = par.param AND
  t.tip = gr.num AND
  t.visible = 1 AND
  t.is_price_invisible = 0 AND
  t.num=tz.tovar_id AND
  tz.sklad_id=#склад_№#
  and not(t.dopoln1 ='' or t.dopoln1 is null )
ORDER BY
  gr.group_name,
  t.name

Назва: Re: Вибірка товарів для прайсу за полем ДП
Відправлено: Creat від Лютий 18, 2013, 17:43:18

тут і мого зеленого поняття хватило
добавляєте внизу запиту t.dopoln1 = '' AND ну або != тобто недорівнює

t.is_price_invisible = 0 AND
t.num=tz.tovar_id AND
t.dopoln1 = 'oleg' AND
tz.sklad_id=#склад_№#

Назва: Re: Вибірка товарів для прайсу за полем ДП
Відправлено: TheOK від Лютий 18, 2013, 17:52:25

Цитата: Creat від Лютий 18, 2013, 17:43:18
тут і мого зеленого поняття хватило
добавляєте внизу запиту t.dopoln1 = '' AND ну або != тобто недорівнює

t.is_price_invisible = 0 AND
t.num=tz.tovar_id AND
t.dopoln1 = 'oleg' AND
tz.sklad_id=#склад_№#

ВО многих случаях можно было бы сделать и так, есть одна особенность. Это можно использовать только если каждое поле ранее редактировалось. Очень часто оно (по умолчанию) = null (вернее значение не определено). Поэтому нужно проверять 2 случая: ='' или =null в синтаксисе языка SQL

Назва: Re: Вибірка товарів для прайсу за полем ДП
Відправлено: mytskaniuk від Лютий 19, 2013, 18:43:48

Дуже дякую, логіка зрозуміла :) буду експериментувати

Спільнота для обміну досвідом між користувачами програм УкрБланк, УкрСклад, УкрЗарплата

Наши программы => УкрСклад => Тема розпочата: mytskaniuk від Лютий 18, 2013, 17:09:07