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

[mytskaniuk]

Доброго дня,

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

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

[TheOK]

Протестируйте, я не проверял.С пустым полем дополнительно 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

[Creat]

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

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

[TheOK]

тут і мого зеленого поняття хватило
добавляєте внизу запиту   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

[mytskaniuk]

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