Формирование SQL-запроса

← →
me2 (2003-06-12 17:33) [0]

Имеются пять таблиц:
1. MATERIALS - справочник материалов с полями
material_code - уник. код
material_name - наименование
...
2. IN_MATERIALS - журнал операций прихода с полями
operation_code - уник. код операции
operation_date - дата операции
...
3 IN_MATERIAL_ITEMS - перечень материала в каждой операции с полями
operation_code - ссылка на IN_MATERIALS."operation_code"
material_code - ссылка на MATERIALS."material_code"
quantity - количество пришедшего материала
4. OUT_MATERIALS - журнал операций расхода с полями
operation_code - уник. код операции
operation_date - дата операции
...
5. OUT_MATERIAL_ITEMS - перечень материала в каждой операции с полями
operation_code - ссылка на OUT_MATERIALS."operation_code"
material_code - ссылка на MATERIALS."material_code"
quantity - количество пришедшего материала

Хочу получить выборку материалов, которые участвовали в операциях прихода/расхода с остатком на некоторое число. Делаю
так
select MATERIALS."material_name", sum(IN_MATERIAL_ITEMS."quantity") - sum(OUT_MATERIAL_ITEMS."quantity") from MATERIALS, IN_MATERIAL_ITEMS, OUT_MATERIAL_ITEMS, IN_MATERIALS, OUT_MATERIALS where (MATERIALS."material_code" = IN_MATERIAL_ITEMS."material_code") and (MATERIALS."material_code" = OUT_MATERIAL_ITEMS."material_code") and (IN_MATERIALS."operation_date" like ("12.06.2003")) and (OUT_MATERIALS."operation_date" like ("12.06.2003")) and (IN_MATERIAL_ITEMS."operation_code" = IN_MATERIALS."operation_code") and (OUT_MATERIAL_ITEMS."operation_code" = OUT_MATERIALS."operation_code") group by MATERIALS."material_name"

Выдает пустой набор данных. Данные в таблицах присутствуют. Помогите исправить ошибку.

← →
Sandman25 (2003-06-12 17:48) [1]

Мне подозрительна конструкция
operation_date" like ("12.06.2003")
Вы дату в виде Char(10) храните? Причем именно в таком виде, с разделителями "."? Зачем там like, можно просто =

← →
me2 (2003-06-12 17:58) [2]

Да действительно в дате была проблема - исправил. Теперь попытался вывести еще дополниттельные поля, т.е.
select MATERIALS."material_code", MATERIALS."material_name", MATERIALS."character_name", MATERIALS."count_descr", MATERIALS."parent", MATERIALS."material_name", sum(IN_MATERIAL_ITEMS."quantity") - sum(OUT_MATERIAL_ITEMS."quantity") from MATERIALS, IN_MATERIAL_ITEMS, OUT_MATERIAL_ITEMS , IN_MATERIALS, OUT_MATERIALS where (MATERIALS."material_code" = IN_MATERIAL_ITEMS."material_code") and (MATERIALS."material_code" = OUT_MATERIAL_ITEMS."material_code") and (IN_MATERIALS."operation_date" = "12.06.2003") and (OUT_MATERIALS."operation_date" = "12.06.2003") and (IN_MATERIAL_ITEMS."operation_code" = IN_MATERIALS."operation_code") and (OUT_MATERIAL_ITEMS."operation_code" = OUT_MATERIALS."operation_code") group by MATERIALS."material_name"

Говорит:
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
invalid column reference.

← →
Sandman25 (2003-06-12 18:17) [3]

Попробуйте
sum(IN_MATERIAL_ITEMS."quantity") - sum(OUT_MATERIAL_ITEMS."quantity")
заменить на
sum(IN_MATERIAL_ITEMS."quantity" - OUT_MATERIAL_ITEMS."quantity")

← →
Sandman25 (2003-06-12 18:19) [4]

И еще.
В Group By придется добавить и добавленные в select поля

← →
me2 (2003-06-12 18:26) [5]

2 Sandman25 © (12.06.03 18:19)
Вот это помогло. Спасибо

← →
me2 (2003-06-13 09:55) [6]

У меня возник еще один вопрос - как получить список всех материалов с остатками на указанное число (т.е. чтобы выводились даже те материалы (с нулевым остатком), которые не участвовали в операциях прихода / расхода )?

← →
HSolo (2003-06-13 10:16) [7]

select ...
from MATERIALS
left outer join IN_MATERIAL_ITEMS on MATERIALS."material_code" = IN_MATERIAL_ITEMS."material_code"
left outer join OUT_MATERIAL_ITEMS on MATERIALS."material_code" = OUT_MATERIAL_ITEMS."material_code"
...
where остальные-условия (даты итд)
Для тех материалов, что не участвовали в операциях, будет выдан null. Если нужен 0 - тогда union, или загнать все это в процедуру и в ней вместо null подставлять 0

← →
me2 (2003-06-13 10:28) [8]

Я делал так:
select MATERIALS."material_code", MATERIALS."material_name", MATERIALS."character_name", MATERIALS."count_descr", MATERIALS."parent", sum(IN_MATERIAL_ITEMS."quantity") - sum(OUT_MATERIAL_ITEMS."quantity") summ from MATERIALS left outer join IN_MATERIAL_ITEMS on MATERIALS."material_code" = IN_MATERIAL_ITEMS."material_code", MATERIALS left outer join OUT_MATERIAL_ITEMS on MATERIALS."material_code" = OUT_MATERIAL_ITEMS."material_code", IN_MATERIALS, OUT_MATERIALS where (IN_MATERIALS."operation_date" = "12.06.2003") and (OUT_MATERIALS."operation_date" = "12.06.2003") and (IN_MATERIAL_ITEMS."operation_code" = IN_MATERIALS."operation_code") and (OUT_MATERIAL_ITEMS."operation_code" = OUT_MATERIALS."operation_code") group by MATERIALS."material_code", MATERIALS."material_name", MATERIALS."character_name", MATERIALS."count_descr", MATERIALS."parent"
И всеравно выдает только записи участвовавшие в операциях. Да еще я по ошибке занес в реализацию больше чем было в приходе. Так минуса не получил, да и вообще ничего не получил - эта запись тоже не была учтена.

← →
me2 (2003-06-13 10:55) [9]

Прошу прощения - запиь которая должна быть с минусом всеже есть в результате запроса, но количество в ней не правильное.

← →
me2 (2003-06-13 11:52) [10]

Народ, ну очеь нужен совет

← →
Sandman25 (2003-06-13 12:00) [11]

Я не спец в SQL с использованием LEFT JOIN и т.д., но мне кажется, что нужно написать LEFT JOIN и перед IN_MATERIALS и OUT_MATERIALS - иначе требуется наличие обязательной связи с ними.

← →
me2 (2003-06-13 12:35) [12]

Да так и есть, но в єтом случае выводится неправильный остаток. Т.е. где-то происходит суммирование несколько раз. Кстати, а почему не работает старый добрый способ *= вместо left outter join, неужели 3 диалект его уже полностью не поддерживает?

← →
Sandman25 (2003-06-13 12:49) [13]

>Кстати, а почему не работает старый добрый способ *= вместо left outter join, неужели 3 диалект его уже полностью не поддерживает?

Извини, я спец только по Informix SQL. Там вообще в данном случае просто outer используется.

← →
HSolo (2003-06-13 14:21) [14]

"Так" делать не надо :)
select <ваш-текст>
from
MATERIALS
left outer join IN_MATERIAL_ITEMS
on MATERIALS."material_code" = IN_MATERIAL_ITEMS."material_code"
left outer join OUT_MATERIAL_ITEMS
on MATERIALS."material_code" = OUT_MATERIAL_ITEMS."material_code"
left outer join IN_MATERIALS
on IN_MATERIAL_ITEMS."operation_code" = IN_MATERIALS."operation_code"
left outer join OUT_MATERIALS
on OUT_MATERIAL_ITEMS."operation_code" = OUT_MATERIALS."operation_code"
where
(IN_MATERIALS."operation_date" = "12.06.2003") and
(OUT_MATERIALS."operation_date" = "12.06.2003")
group by <ваш-текст>

И если у Вас Interbase - забудьте про "старый добрый *=". Это не есть стандартный синтаксис, такое поддерживает, если не ошибаюсь, Oracle, а кто еще - даже не знаю. В IB такого нет.

← →
me2 (2003-06-13 15:17) [15]

2 HSolo © (13.06.03 14:21)
Не не работает - все равно только те материалы, которые участвовали в опрациях, да и с неправильнами остатками

← →
me2 (2003-06-13 15:18) [16]

2 HSolo © (13.06.03 14:21)
Не не работает - все равно только те материалы, которые участвовали в опрациях, да и с неправильными остатками

← →
HSolo (2003-06-13 15:34) [17]

Прошу прощения, соврамши...
select MATERIALS."material_code", MATERIALS."material_name",
MATERIALS."character_name", MATERIALS."count_descr",
MATERIALS."parent",
sum(IN_MATERIAL_ITEMS."quantity") - sum(OUT_MATERIAL_ITEMS."quantity") summ
from
MATERIALS
left outer join IN_MATERIAL_ITEMS
on MATERIALS."material_code" = IN_MATERIAL_ITEMS."material_code"
left outer join OUT_MATERIAL_ITEMS
on MATERIALS."material_code" = OUT_MATERIAL_ITEMS."material_code"
left outer join IN_MATERIALS
on MATERIALS."material_code" = IN_MATERIALS."material_code"
and IN_MATERIAL_ITEMS."operation_code" = IN_MATERIALS."operation_code"
left outer join OUT_MATERIALS
on MATERIALS."material_code" = OUT_MATERIALS."material_code"
and OUT_MATERIAL_ITEMS."operation_code" = OUT_MATERIALS."operation_code"
where
(IN_MATERIALS."operation_date" = "12.06.2003") and
(OUT_MATERIALS."operation_date" = "12.06.2003")
group by MATERIALS."material_code", MATERIALS."material_name", MATERIALS."character_name",
MATERIALS."count_descr", MATERIALS."parent"

← →
me2 (2003-06-13 15:41) [18]

2 HSolo © (13.06.03 15:34
>...IN_MATERIALS."material_code"... ...OUT_MATERIALS."material_code"...

В єтих таблицах нет таких полей. OUT_MATERIALS и IN_MATERIALS связаны по "operation_code" с OUT_MATERIAL_ITEMS и IN_MATERIAL_ITEMS соответственно, а вот в них уже есть "material_code"

← →
HSolo (2003-06-13 15:51) [19]

Тогда так:
select MATERIALS."material_code", MATERIALS."material_name",
MATERIALS."character_name", MATERIALS."count_descr",
MATERIALS."parent",
sum(IN_MATERIAL_ITEMS."quantity") - sum(OUT_MATERIAL_ITEMS."quantity") summ
from
MATERIALS
left outer join IN_MATERIAL_ITEMS
on MATERIALS."material_code" = IN_MATERIAL_ITEMS."material_code"
left outer join OUT_MATERIAL_ITEMS
on MATERIALS."material_code" = OUT_MATERIAL_ITEMS."material_code"
left outer join IN_MATERIALS
on IN_MATERIAL_ITEMS."operation_code" = IN_MATERIALS."operation_code"
left outer join OUT_MATERIALS
on OUT_MATERIAL_ITEMS."operation_code" = OUT_MATERIALS."operation_code"
where
(IN_MATERIALS."operation_date" = "12.06.2003") and
(OUT_MATERIALS."operation_date" = "12.06.2003")
group by MATERIALS."material_code", MATERIALS."material_name", MATERIALS."character_name",
MATERIALS."count_descr", MATERIALS."parent"

← →
HSolo (2003-06-13 15:55) [20]

Еще раз извините
select MATERIALS."material_code", MATERIALS."material_name",
MATERIALS."character_name", MATERIALS."count_descr",
MATERIALS."parent",
sum(IN_MATERIAL_ITEMS."quantity") - sum(OUT_MATERIAL_ITEMS."quantity") summ
from
MATERIALS
left outer join IN_MATERIAL_ITEMS
on MATERIALS."material_code" = IN_MATERIAL_ITEMS."material_code"
left outer join OUT_MATERIAL_ITEMS
on MATERIALS."material_code" = OUT_MATERIAL_ITEMS."material_code"
left outer join IN_MATERIALS
on IN_MATERIAL_ITEMS."operation_code" = IN_MATERIALS."operation_code"
and (IN_MATERIALS."operation_date" = "12.06.2003")
left outer join OUT_MATERIALS
on OUT_MATERIAL_ITEMS."operation_code" = OUT_MATERIALS."operation_code"
and (OUT_MATERIALS."operation_date" = "12.06.2003")
group by MATERIALS."material_code", MATERIALS."material_name", MATERIALS."character_name",
MATERIALS."count_descr", MATERIALS."parent"

а то опять не выдаст те материалы, что не участвовали в операциях

← →
me2 (2003-06-13 16:07) [21]

Данный запрос отлично работает только до тех пор, пока в таблицах прихода и расхода (IN_MATERIAL_ITEMS, OUT_MATERIAL_ITEMS) по одной записи. Например, у меня пришло 3434 + 100, ушло 12, а запрос выдает, что осталось 3510, т.е получается, что берется два раза из таблицы расхода по 12.

← →
MsGuns (2003-06-13 16:20) [22]

Самый лучший способ в этом случае (ИМХО) - использование JOUN с выводом в рез.набор пары наборов полей (кол-во, цена, сумма) для вх и вых док-тов (операций). А вообще-то я бы не рекомендовал заводить для каждого типа операции (вида документа: прихода, отгрузки, перемещения, списания и т.д.) отдельную таблицу. Все прекрасно держится в одной паре таблиц:
1. Заголовки (дата, №, тип док-та, суммы и т.д.)
2. Фактура (код, кол-во, сумма и т.д.), причем сумма и кол-во
положительные для приходных или или отрицательных для расходных документов.

← →
me2 (2003-06-13 16:47) [23]

2 MsGuns © (13.06.03 16:20)
Отличый совет. Спасибо. Сейчас займусь переделкой. Но мне все-таки интересно - можно ли добиться того чего я хочу на существующей структуре БД?

← →
MsGuns (2003-06-13 16:55) [24]

>me2 (13.06.03 16:47)

Можно. Используя Outer|Inner JOIN или аккумулятор UNION

← →
Sandman25 (2003-06-13 17:02) [25]

me2

Все логично. Вы не можете суммировать в одном select и приход, и расход. Вот почему - например, у вас 1 приход (количество единиц 1) и 6 расходов (количество 5 в каждом). Тогда любой запрос найдет этот единственный приход, а затем будет перебирать все 6 расходов, заодно суммируя приход целых 6 раз (вместо 1).
В общем, нужно либо делать join, либо перепроектировать базу, храня все движение в одной таблице (приход с плюсом, расход с минусом).

← →
me2 (2003-06-13 17:08) [26]

2 MsGuns © (13.06.03 16:55), Sandman25 © (13.06.03 17:02)

Так я использую join да и советов давали уже много, только
некоректно это все работает. Может Вы подскажите как правильно это сделать?

← →
me2 (2003-06-13 17:14) [27]

Да еще вопрос:
У меня в таблицах заголовках IN_MATERIALS и OUT_MATERIALS было поле-ссылка на номер поставщика и на номер потребителя соответственно. Как правильно поступить при использовании одной таблицы - хранить оба номера, для каждой записи один из которых будет пуст?

← →
Sandman25 (2003-06-13 17:15) [28]

Я вообще бы использовал хранимую процедуру и временную таблицу.
create temp table tmp_materials ( mater_code char(10), mater_name char(40), prihod dec(18,2), rashod dec(18,2) ) with no log; insert into tmp_materials (mater_code, mater_name) select material_code, material_name from materials; update tmp_materials set prihod = (select sum(p.quantity) from in_material_items p where p.material_code = tmp_materials.material_code; update tmp_materials set prihod = 0 where prihod is null; // если надо update tmp_materials set rashod = (select sum(p.quantity) from out_material_items p where p.material_code = tmp_materials.material_code; update tmp_materials set rashod = 0 where rashod is null; // если надо

← →
Sandman25 (2003-06-13 17:20) [29]

>Как правильно поступить при использовании одной таблицы - хранить оба номера, для каждой записи один из которых будет пуст?

Можно так, а можно и в одном поле. Все зависит от того, какие запросы потом будут использоваться.

← →
me2 (2003-06-13 17:27) [30]

> create temp table

Разве в IB такое есть?

← →
Sandman25 (2003-06-13 17:37) [31]

>Разве в IB такое есть?

Наверное, нет.

Напишите хранимую процедуру, вытаскивайте в цикле все материалы (по одному), рассчитывайте rashod, prihod и делайте suspend.
А из delphi вызывайте select * from proc_name(...)

PS. Я же предупреждал, что я на IB не работаю, поэтому могу дать только идею.

← →
MsGuns (2003-06-13 17:39) [32]

>me2 (13.06.03 17:14)
>У меня в таблицах заголовках IN_MATERIALS и OUT_MATERIALS было поле-ссылка на номер поставщика и на номер потребителя соответственно. Как правильно поступить при использовании одной таблицы - хранить оба номера, для каждой записи один из которых будет пуст?

Поставщик и Потребитель может быть одно и тоже лицо (фирма, организация). Поэтому если в БД иметь их как 2 РАЗНЫЕ сущности (и соотв-но 2 РАЗНЫХ спр-ка), то невозможно будет получить, к примеру, сводный баланс по партнеру.

← →
me2 (2003-06-13 17:41) [33]

2 Sandman25 © (13.06.03 17:20)

> Все зависит от того, какие запросы потом будут использоваться

Т.е. проверяем поле типа операции и в зависиомти от результата имеем либо поставщика либо потребителя - так?

← →
me2 (2003-06-13 17:45) [34]

2 MsGuns © (13.06.03 17:39)

Как Вы посоветуете хранить данные о Поставщиках/Потребителях (с привязкой к операциям прихода и расхода)?

← →
Sandman25 (2003-06-13 17:46) [35]

← →
MsGuns (2003-06-13 18:45) [36]

>me2 (13.06.03 17:45)

Схем достаточно много. Все зависит от целей и средств ;))
Вообще-то есть 2 более-менее стандартные схемы:
1. Журнал
2. Счет-сальдо

При первой по Сущности "Счет-Субсчет" ведутся записи по дебету и кредиту, куда практически попадают все строки операций (фактур). При этом таблиц документов (2-й уровень) можно вообще не держать. Т.е. документы при необходимости "собираются" их журнала для просмотра или редактирования.

При второй документы как таковые присутствуют как таблицы, а в карточки "Счет-Субсчет" прописываются только данные из заголовков операций, т.е. суммарные обороты, которые модифицируют сальдо. Там же (в карточках) отражается и разноска проплат.

Склады выступают либо как отдельные сущности, либо как "партнеры" по общей схеме (в зависимости от внутр.особенностей движения ТМЦ, например есть ли перемещение между складами, есть ли розница и какая и т.д.)

Очень вкратце, возможно, не совсем точно, но я думаю, что, поразмыслив, можно "дорисовать" себе детали схемы.

← →
me2 (2003-06-13 18:52) [37]

Посмотрите me2 (13.06.03 17:45)

← →
me2 (2003-06-13 18:53) [38]

← →
me2 (2003-06-14 10:54) [39]

Я поступил как советовал MsGuns © (13.06.03 16:20)
Использую запрос
select materials."material_name", materials."character_name", materials."count_descr", materials."price", sum(operation_items."quantity") from materials left outer join operation_items on materials."material_code" = operation_items."material_code" left outer join operations on operation_items."operation_code" = operations."operation_code" and (operations."operation_date" = "11.06.2003") group by materials."material_name", materials."character_name", materials."count_descr", materials."price"
Запрос не учитывает дату, т.е. выводятся записи не зависимо от даты операции. Что не так?

← →
me2 (2003-06-14 11:24) [40]

Я могу использовать хранимую процедуру, которая для каждого материала (входной параметр) будет выдавать его остаток на указанное число (тоже входной параметр). Потом мне надо на клиенте отображая список материалов видить остатки. Для DataSet можно создать вычисляемое поле и в OnCalcFields вызывать эту хранимую процедуру. Вопрос в том не будет ли это слишком большой нагрузгой на сервер и сеть?

Формирование SQL-запроса Найти похожие ветки