А что если SUM(Table.Field) = null?

← →
KIR © (2003-10-14 11:48) [0]

Народ, ситуация следующая: требуется сделать так:
SELECT SUM(TABLE1.FIELD)+SUM(TABLE2.FIELD) AS TOTALSUM FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.ID = TABLE2.TABLE1ID
В некоторых строках получается, что SUM(TABLE2.FIELD) = NULL, тогда и SUM(TABLE1.FIELD)+SUM(TABLE2.FIELD) тоже получается равной null, а надо чтоб было SUM(TABLE1.FIELD). Причем, если сделать так:
SELECT SUM(TABLE1.FIELD) AS SUM1, SUM(TABLE2.FIELD) AS SUM2 FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.ID = TABLE2.TABLE1ID
то все нормально, но два поля, а нодо все в одно...

← →
Sergey13 © (2003-10-14 11:53) [1]

Правильнее на такие поля ставить NOT NULL и дефолтом присваивать 0. Траблы уйдут в прошлое.

← →
Danilka © (2003-10-14 11:54) [2]

в дополнение к: [1] Sergey13 © (14.10.03 11:53)
причем, это никогда не поздно исправить.

← →
Shura (2003-10-14 11:56) [3]

Операции с Null в итоге дают Null.
Например, в Oracle есть функция NVL(x, y) - возвращает Х, если Х<>Null, иначе Y. В данном случае надо
NVL(SUM(TABLE1.FIELD), 0)+NVL(SUM(TABLE2.FIELD), 0) as TotalSum.
Аналог в IB не знаю, не пользовался :(, но смысл такой. Конвертация Null в 0 нужна.

← →
DenK_vrtz © (2003-10-14 11:57) [4]

либо делать преобразование неопределенного значеня, но лучше делать как советуют Sergey13 © и Danilka ©

← →
Johnmen © (2003-10-14 12:03) [5]

Даже если и сделать поля дефолтно = 0, может быть ситуация, когда SUM по присоединяемой с правого бока табл. (см.запрос) будет NULL.
1. Использовать UDF
2. Суммировать SUM1+SUM2 в приложении...

← →
KIR © (2003-10-14 12:08) [6]

>Sergey13 © (14.10.03 11:53) [1]
Если Вы внимательно прочтете запрос, то увидите, что SUM(TABLE2.FIELD) является NULL в том случае, если нет ни одной записи в TABLE2 соответствующей определенной записи в TABLE1. Какой тут может быть NOT NULL и дефолтом 0, если поле вычисляется в прцессе выполнения запроса...

← →
Johnmen © (2003-10-14 12:18) [7]

[5]

← →
Val © (2003-10-14 12:18) [8]

>KIR © (14.10.03 12:08) [6]
см.Johnmen © (14.10.03 12:03) [5] п.1.

← →
Romkin © (2003-10-14 12:30) [9]

2Johnmen 3. Написать ХП и не мучится

← →
Johnmen © (2003-10-14 12:41) [10]

>Romkin © (14.10.03 12:30)

Да, тоже хороший вариант :)
И в данном случае, пожалуй, самый приемлемый.

← →
KIR © (2003-10-14 13:47) [11]

Я решил задачу так: вывожу итоговый НД не в DBGrid"e а в стандартном TStringGrid"e (сделал процедуру FormGrid и в ней все обрабатываю). Единственный косячок - TStringGrid внешне немного отличается от TDBGrid"a, ну и, естественно, пропала возможность использовать FIBPlus"овскую Master-Detail связку. Приходится открывать DetailDS по SelectCell StringGrid"a...

Простите, что не догоняю, но как в моем случае мне поможет ХП? Мне требуется вернуть НД и отобразить его в DBGrid"e...

← →
Danilka © (2003-10-14 13:49) [12]

[11] KIR © (14.10.03 13:47)
1. ХП может возвращать НД
2. Если на клиенте, то проще и правильнее (на мой взгляд) сделать вычисляемое поле, чем, все тащить в стрингГрид.

← →
Romkin © (2003-10-14 14:01) [13]

Вот пример: имеем
SELECT SUM(TABLE1.FIELD)+SUM(TABLE2.FIELD) AS TOTALSUM FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.ID = TABLE2.TABLE1ID

Надо, чтобы NULL == 0, делаем
create procedure List_Full_sum returns (FullSun <FieldType>) as declare variable FIELD2 <FiELDTYPE>; declare variable ID <FiELDTYPE>; begin for select ID, FIELD from Table1 into :ID, :FullSum do begin if (FullSum is NULL) then FullSum = 0; FIELD2 = NULL; /* Если TABLE1ID - с уникальным индексом */ select FIELD2 from Table2 where TABLE1ID = :ID into :FIELD2; if (FIELD2 is NULL) then FIELD2 = 0; FullSum = FullSum + FIELD2; suspend; end end
Если с TABLE1ID может быть много записей - тоже не проблема
create procedure List_Full_sum returns (FullSun <FieldType>) as declare variable FIELD1 <FiELDTYPE>; declare variable FIELD2 <FiELDTYPE>; declare variable ID <FiELDTYPE>; begin for select ID, FIELD from Table1 into :ID, :FIELD1 do begin if (FIELD1 is NULL) then FIELD1 = 0; /* Если TABLE1ID - не является ключем */ for select FIELD2 from Table2 where TABLE1ID = :ID into :FIELD2 do begin if (FIELD2 is NULL) then FIELD2 = 0; FullSum = FIELD1 + FIELD2; suspend; end end end
ТАк понятно?
Вызов, ессно, элементарен:
select * from List_Full_sum

← →
Johnmen © (2003-10-14 14:22) [14]

>Romkin © (14.10.03 14:01)

Забыл проинициализировать FullSum нулевым значением и
FullSum = FullSum + FIELD1 + FIELD2;
да и suspend нужен в самом конце, вне циклов (если хотим select"ом из процедуры).

← →
Romkin © (2003-10-14 14:24) [15]

Зачем? Сумма вроде не накопительная, я чисто перевел select. А зачем suspend в конце? Если ничего нет - ничего и не вернет

← →
Johnmen © (2003-10-14 14:46) [16]

Как не накопительная ?
В запросе явно видно, что ожидается одна запись...:)

← →
Romkin © (2003-10-14 14:47) [17]

Ой!!!! млин...

← →
Romkin © (2003-10-14 14:49) [18]

Исправляюсь: вызов select sum(FullSum) as TotalSum from List_Full_sum
^)))))

← →
kaif © (2003-10-14 15:08) [19]

Судя по вопросу, нужно получить более, чем 1 запись и отобразить в гриде.
Я бы сделал очень просто.
Сделал бы второй (из приведенных) запрос:

SELECT
SUM(TABLE1.FIELD) AS SUM1,
SUM(TABLE2.FIELD) AS SUM2
FROM
TABLE1 LEFT OUTER JOIN TABLE2
ON TABLE1.ID = TABLE2.TABLE1ID

создал бы в приложении persistent поля у датасета и добавил бы еще вычисляемое поле SUM3 в датасет.
(Поле типа ftCalculated).
И написал бы в обработчик OnCalcField:

with DataSet do
FieldsByName("SUM1").AsCurrency :=
FieldsByName("SUM2").AsCurrency +
FieldsByName("SUM3").AsCurrency;

И хотя я большой любитель написания ХП (Johnmen не даст соврать :), я в данном случае обошелся бы без них.

Пардон:
with DataSet do
FieldsByName("SUM3").AsCurrency :=
FieldsByName("SUM1").AsCurrency +
FieldsByName("SUM2").AsCurrency;

Если рез-т нужен для отображения, то идея kaif © (14.10.03 15:08) [19] , только можно не мучиться с калькулируемыми полями, а просто использовать событие OnGetText любого из суммовых полей, происывая туда результат сложения, заменяя отсутствующие суммы нулями процедурно.

MsGuns © (14.10.03 15:15) [21]
Если рез-т нужен для отображения, то идея kaif © (14.10.03 15:08) [19] , только можно не мучиться с калькулируемыми полями, а просто использовать событие OnGetText

Дело вкуса, однако...

Можно не мучиться с событиями OnGetText, а просто использовать вычисляемое поле. :)
Тем более, что у вычисляемого поля есть преимущество: его можно не только отображать, но и обращаться потом к нему с целью получения данных (например, при генерации отчета) так же, как и к обычному полю.
Я лично редко использую OnGetText (только когда нужно как-то нетривиально форматировать информацию). Хотя, возможно, я и не прав.
С уважением, kaif

← →
SasaR (2003-10-14 16:41) [23]

Написать SP типа is_null(val1,val2)
----------
select (is_null(SUM(XXX),0)).....

>kaif © (14.10.03 16:32) [22]

Я написал "мучиться", имея в виду доп. фрикции с датасетом в дизайне. Мне кажется, что в событии проще и, что немаловажно, нагляднее показать алгоритм преобразования данных для отображения.

С неменьшим уважением ;))

Думаю, тут самый лучший выход - [23]
Аналог функции NVL в Оракле.

Народ, всем спасибо! В итоге я все сделал через ХП (спасибо Romkin © (14.10.03 12:30) [9] за идею). Решил делать через ХП, потому что то, что я выложил в форум - только небольшая часть запроса - в реальности все гораздо сложнее.

>SasaR (14.10.03 16:41) [23] но, насколько мне известно, IB не позволяет создавать UDF с NULL-параметрами...

← →
SasaR (2003-10-15 15:11) [28]

Я имел ввиду не UDF а обычную сохранённую процедуру :)))

А что если SUM(Table.Field) = null? Найти похожие ветки