Вызов SQL-функции с меняющимся аргументом

← →
И. Павел (2009-03-19 10:23) [0]

Здравствуйте.
Пишу запрос для работы с MS SQL Server. В запросе вызывается функция Func (), написанная в SQL SERVER в разделе User defined functions, которая принимает один аргумент типа int. Если написать так:

ADOQ.SQL.ADD(‘ SELECT tab.id, F.rez ’+ ‘ FROM tab ’ ‘ LEFT JOIN Func(123) AS F ON F.id=tab.id ‘)

то все работает, но для каждой записи в tab нужно вызывать функцию со своим аргументом:

ADOQ.SQL.ADD(‘ SELECT tab.id, F.rez ’+ ‘ FROM tab ’ ‘ LEFT JOIN Func(tab.id) AS F ON F.id=tab.id ‘)

при этом SQL Server выдает ошибку: “Incorrect Syntax near .”,
видимо по поводу выражения “Func(tab.id)”.

Если использовать параметры:

if Query.Parameters.FindParam(‘Arg’)=nil then Query.Parameters.AddParameter.Name:=’Arg’; Query.Parameters.ParamByName(’Arg’).DataType:=ftString; Query.Parameters.ParamByName(’Arg’).Value:=’tab.id’; Query.SQL.Add(‘SELECT * FROM tab LEFT JOIN Func(:Arg)’);

то вылетает ошибка, сообщающая о том, что ожидалось целое число, а пришла строка, то есть, видимо, в функцию Func сразу же передается выражение ‘tab.id’, а мне нужно, чтобы передавалось значение этого выражения.

Подскажите, пожалуйста, как сделать такой запрос, в котором вызывалась бы функция с меняющимся аргументом?

← →
ЮЮ © (2009-03-19 10:35) [1]

LEFT JOIN Func(tab.id) AS F ON F.id=tab.id

перепиши Func так, чтобы она была без параметров, но возвращала множество записей, а не одну, т.е. "полноценную таблицу", а уж затем связывай таблицы tab и Func AS F

Или Func() должна быть скалярной функцией и синтаксис будет другой

SELECT tab.id, Func(tab.id) as rez FROM tab

← →
И. Павел (2009-03-19 11:42) [2]

> ЮЮ ©

Спасибо. Сразу набирать большую таблицу – это выход. Только если такая функция будет медленно выполняться, возникнет таймаут.
Я пробовал увеличить время ожидания:

ADOConnection1.Close;
ADOConnection1.ConnectionTimeout:=1000000;
ADOConnection1.Open;
ADOConnection1.CommandTimeout:=1000000;

Но это ни к чему не приводит – предельное время выполнения запроса по прежнему остается равно 30 сек. Можно ли как-нибудь увеличить время ожидания, или вообще запретить таймауты?

← →
stas © (2009-03-19 13:22) [3]

из справки к mssql 2005
SELECT O.[Date], P.Model, P.Configuration, P.Price FROM Orders O
OUTER APPLY GetProductDetails(O.ProductID) AS P

Но не думаю что это будет работать более быстро

← →
sniknik © (2009-03-19 13:49) [4]

> Можно ли как-нибудь увеличить время ожидания,
можно. но менять нужно именно время таймаута запроса, а не соединения...
ну и плюс в первых версиях ADO (вернее дельфевской обертки) были с ними глюки, приходилось лезть в генофонд... но потом стало все нормально.
блин, а вот для чего при посте вопроса в форум там стоят версии дельфей для указания используемой????

> или вообще запретить таймауты?
значение 0.

← →
И. Павел (2009-03-19 15:54) [5]

> stas ©

Почему-то

SELECT * FROM tab LEFT OUTER JOIN APPLY Func(1234) F ON 1=1

тоже возвращает ошибку.

> sniknik ©

Спасибо. Поменял таймаут в TADOQuery - заработало. Пишу на Delphi 2005.

← →
Ю.Ю. (2009-03-19 22:11) [6]

>
> тоже возвращает ошибку.

а сервер у тебя 2005?

← →
stas © (2009-03-19 22:25) [7]

Join ненадо

← →
И. Павел (2009-03-20 08:36) [8]

> Ю.Ю. (19.03.09 22:11) [6]
>
> >
> > тоже возвращает ошибку.
>
> а сервер у тебя 2005?

У меня SQL SERVER 2000. Можно ли в нем как нибудь выполнить такой запрос? Кроме моей программы с SQL сервером работает еще много других приложений, и вряд ли те, кто администрируют SQL SERVER будут его обновлять по моей просьбе...

> stas © (19.03.09 22:25) [7]
> Join ненадо

Пытался сделать так:

SELECT * FROM tab
OUTER APPLY Func(tab.id)
Ошибка: Incorrect syntax near the keyword "OUTER".

SELECT * FROM tab
LEFT OUTER APPLY Func(tab.id)

Ошибка: "APPLY" is not a recognized join option.

Все дело в старом SQL SERVER, или я просто не правильно составляю запрос? Если второе, подскажите, пожалуйста, как правильно?

← →
stas © (2009-03-20 08:52) [9]

И. Павел (20.03.09 08:36) [8]
нет надо 2005 SQL.
Только из-за этого не стоит переустанавливать SQL.

← →
ЮЮ © (2009-03-20 09:25) [10]

> Только если такая функция будет медленно выполняться, возникнет
> таймаут.

Может функцию покажешь?

← →
sniknik © (2009-03-20 10:32) [11]

и еще объясни чего ты хочеш добиться от запроса,
SELECT tab.id, F.rez
FROM tab
LEFT JOIN Func(tab.id) AS F ON F.id=tab.id
идеологию т.сказать.

джойн объединяет рекордсеты, т.е. то что уже имеется до запроса (даже подзапрос если он там стоит, выполняется до основного).
а что должно в твоем случае происходить? объединение с кучей рекордсетов? (т.е. по одному на каждую итерацию цикла выборки) как ты вообще представляешь что должно получиться от твоей конструкции?
"на пальцах", т.е. приведи пару записей + результат функции, и что должно быть в итоге.

← →
И. Павел (2009-03-20 10:51) [12]

Функция для одной задачи такая (переводит, главным образом, числа в проценты, например было 2.5, 2.5 и 5, в результате получается 25%, 25% и 100%):

CREATE FUNCTION dbo.GetVesa (@zadacha int) RETURNS @TabVesa TABLE ( idzadacha int, begin_status datetime, status int, tab_num int, fio varchar(50), fct float, ych_fct int, pl float, ych_pl int, ych1 int, ych2 int, ych3 int, ocenka1 int, ves1 float, ocenka2 int, ves2 float ) AS BEGIN DECLARE @norma int; DECLARE @otv int; DECLARE @ocenka1 int; DECLARE @ocenka2 int; DECLARE @VesChasa float; DECLARE @VesZadachi float; DECLARE @stat int; DECLARE @BegStat datetime; DECLARE @cur_tab_num int; DECLARE @cur_fct float; DECLARE @cur_pl float; DECLARE @SumFct float; DECLARE @SumPl float; DECLARE @SumYchFct int; DECLARE @SumYchPl int; DECLARE @MaxFctTabNum int; DECLARE @MaxPlTabNum int; SELECT @norma=zadachi.norma, @otv=historyzadachi2.otv, @ocenka1=zadachi.ocenka1, @ocenka2=zadachi.ocenka2, @VesChasa=spr_vesa.veschasa, @stat=historyzadachi2.status, @BegStat=historyzadachi2.dates FROM zadachi LEFT JOIN historyzadachi2 ON historyzadachi2.datePo="9999.12.31" AND historyzadachi2.id_zadachi=@zadacha LEFT JOIN spr_vesa ON spr_vesa.tipzadachi=zadachi.tipzadachi AND spr_vesa.scale=zadachi.scale LEFT JOIN sotrudniki ON sotrudniki.tab_num=historyzadachi2.otv WHERE zadachi.id=@zadacha GROUP BY zadachi.norma, historyzadachi2.otv, zadachi.ocenka1, zadachi.ocenka2, spr_vesa.veschasa, historyzadachi2.status, historyzadachi2.dates; SELECT @VesZadachi=@VesChasa*@norma; INSERT INTO @TabVesa(idzadacha, begin_status, status, tab_num, fio, fct, pl, ych1, ych2, ych3, ocenka1, ves1, ocenka2, ves2) SELECT @zadacha, @BegStat, @Stat, sotrudniki.tab_num, sotrudniki.fio, isnull(sum(fact.fact), 0), isnull( ( SELECT isnull(sum(AAhistoryzadania2.trudoemkost), 0) FROM plan_sotr AS AAplan_sotr LEFT JOIN historyzadania2 AS AAhistoryzadania2 ON AAhistoryzadania2.datepo="9999.12.31" AND AAhistoryzadania2.id_zadania=AAplan_sotr.id WHERE AAplan_sotr.sotrudnik=sotrudniki.tab_num AND AAplan_sotr.namez=@zadacha GROUP BY AAplan_sotr.namez ), 0), isnull(vesa.ych1, 0), isnull(vesa.ych2, 0), isnull(vesa.ych3, 0), @ocenka1, (@ocenka1-3)*@VesZadachi*0.05, @ocenka2, (@ocenka2-3)*@VesZadachi*0.05 FROM sotrudniki LEFT JOIN plan_sotr ON plan_sotr.sotrudnik=sotrudniki.tab_num AND plan_sotr.namez=@zadacha LEFT JOIN vesa ON vesa.namez=@zadacha AND vesa.sotrudnik=sotrudniki.tab_num LEFT JOIN historyzadania2 ON historyzadania2.datepo="9999.12.31" AND historyzadania2.id_zadania=plan_sotr.id LEFT JOIN fact ON fact.idplsotr=plan_sotr.id WHERE sotrudniki.tab_num=@otv GROUP BY sotrudniki.tab_num, sotrudniki.fio, vesa.ych1, vesa.ych2, vesa.ych3 UNION SELECT @zadacha, @BegStat, @Stat, sotrudniki.tab_num, sotrudniki.fio, isnull(sum(fact.fact), 0), isnull( ( SELECT isnull(sum(AAhistoryzadania2.trudoemkost), 0) FROM plan_sotr AS AAplan_sotr LEFT JOIN historyzadania2 AS AAhistoryzadania2 ON AAhistoryzadania2.datepo="9999.12.31" AND AAhistoryzadania2.id_zadania=AAplan_sotr.id WHERE AAplan_sotr.sotrudnik=sotrudniki.tab_num AND AAplan_sotr.namez=@zadacha GROUP BY AAplan_sotr.namez ), 0), isnull(vesa.ych1, 0), isnull(vesa.ych2, 0), isnull(vesa.ych3, 0), 0, 0, 0, 0 FROM sotrudniki INNER JOIN plan_sotr ON plan_sotr.sotrudnik=sotrudniki.tab_num AND plan_sotr.namez=@zadacha LEFT JOIN vesa ON vesa.namez=@zadacha AND vesa.sotrudnik=sotrudniki.tab_num LEFT JOIN historyzadania2 ON historyzadania2.datepo="9999.12.31" AND historyzadania2.id_zadania=plan_sotr.id LEFT JOIN fact ON fact.idplsotr=plan_sotr.id WHERE sotrudniki.tab_num<>@otv GROUP BY sotrudniki.tab_num, sotrudniki.fio, vesa.ych1, vesa.ych2, vesa.ych3 ORDER BY sotrudniki.fio ASC; SELECT @SumFct=(SELECT isnull(sum(fct), 0) FROM @TabVesa); SELECT @SumPl=(SELECT isnull(sum(pl), 0) FROM @TabVesa); SELECT @MaxFctTabNum=(SELECT TOP 1 tab_num FROM @TabVesa ORDER BY fct DESC); SELECT @MaxPlTabNum=(SELECT TOP 1 tab_num FROM @TabVesa ORDER BY pl DESC); if @SumFct<>0 Begin UPDATE @TabVesa SET ych_fct=round(fct*100/@SumFct, 0) WHERE tab_num<>@MaxFctTabNum; SELECT @SumYchFct=(SELECT isnull(sum(ych_fct), 0) FROM @TabVesa WHERE tab_num<>@MaxFctTabNum); UPDATE @TabVesa SET ych_fct=100-@SumYchFct WHERE tab_num=@MaxFctTabNum; end else UPDATE @TabVesa SET ych_fct=0; if @SumPl<>0 Begin UPDATE @TabVesa SET ych_pl=round(pl*100/@SumPl, 0) WHERE tab_num<>@MaxPlTabNum; SELECT @SumYchPl=(SELECT isnull(sum(ych_pl), 0) FROM @TabVesa WHERE tab_num<>@MaxPlTabNum); UPDATE @TabVesa SET ych_pl=100-@SumYchPl WHERE tab_num=@MaxPlTabNum; end else UPDATE @TabVesa SET ych_pl=0; RETURN; END

← →
И. Павел (2009-03-20 10:52) [13]

Общая функция, выбирающая большое число полей:

CREATE FUNCTION dbo.GetForShowVesa() RETURNS @TabAllVesa TABLE ( idzadacha int, begin_status datetime, status int, tab_num int, fio varchar(50), fct float, ych_fct int, pl float, ych_pl int, ych1 int, ych2 int, ych3 int, ocenka1 int, ves1 float, ocenka2 int, ves2 float ) AS BEGIN DECLARE @id int; DECLARE @Tab123 TABLE ( idzadacha int, begin_status datetime, status int, tab_num int, fio varchar(50), fct float, ych_fct int, pl float, ych_pl int, ych1 int, ych2 int, ych3 int, ocenka1 int, ves1 float, ocenka2 int, ves2 float ) DECLARE CZadachiId CURSOR READ_ONLY FOR SELECT DISTINCT namez FROM Vesa; OPEN CZadachiId; FETCH NEXT FROM CZadachiId INTO @id; WHILE (@@FETCH_STATUS = 0) BEGIN INSERT INTO @TabAllVesa(idzadacha, begin_status, status, tab_num, fio, fct, ych_fct, pl, ych_pl, ych1, ych2, ych3, ocenka1, ves1, ocenka2, ves2) SELECT V.idzadacha, V.begin_status, V.status, V.tab_num, V.fio, V.fct, V.ych_fct, V.pl, V.ych_pl, V.ych1, V.ych2, V.ych3, V.ocenka1, V.ves1, V.ocenka2, V.ves2 FROM GetVesa(@id) AS V FETCH NEXT FROM CZadachiId INTO @id; END CLOSE CZadachiId; DEALLOCATE CZadachiId; Return; end

← →
И. Павел (2009-03-20 10:52) [14]

В дальнейшем база данных будет расти, и функция будет работать все медленнее. Пользователь будет читать таблицу не полностью, а выбирать определенные записи через фильтр. Поэтому я хочу сделать такой запрос, в котором функция считалась бы только для записей, которые будут выводиться на экран. Сейчас я пишу скалярные функции, так фильтр будет работать, но для расчета поля A требуется вызвать GetVesa1(...) а для расчета поля B нужно GetVesa1(...)*100, и тогда функция GetVesa1 будет выполняться 2 раза для каждой записи (хотя это лучше, так как только для отфильтрованных записей).

> sniknik ©

Запрос довольно большой, использую FROM LEFT JOIN LEFT JOIN ... я выбераю много полей из разных таблиц, связанных между собой. Теперь для каждой записи мне нужно добавить несколько полей, которые вычисляет функция GetVesa.

Записи примерно такие:
задача сотрудник [информация о сотруднике, задаче] % вес
1 1234 ... 10 10*X
1 123 ... 20 20*X
2 345 ... 70 70*X

Кроме этих полей вычисляются и другие. Идеальным вариантом было бы считать все эти поля в одной функции, так как они взаимосвязаны.

> Идеальным вариантом было бы считать все эти поля в одной функции, так как они взаимосвязаны.
ну так, если ты хочешь считать все в одной, то должен передавать внутрь список обсчитываемых ей данных, а не вызывать ее для каждого значения...

+ вызов вернет рекордсет.
повторю вопрос. как ты думаешь должно работать объединение если вместо одного раза в начале ему будут подсовывать рекордсет на каждую итерацию?

или поменять логику.
см. ЮЮ © (19.03.09 10:35) [1]

> Пользователь будет читать таблицу не полностью, а выбирать
> определенные записи через фильтр

надеюсь через WHERE, а не локальный фильтр :)

Как вариант, добавлять параметры фильтра в параметры функции и использовать из в WHERE запроса, выбирающего записи, подлежащие обсчёту.

Хотя главный запрос
SELECT ...
FROM sotrudniki
WHERE sotrudniki.tab_num=@otv
UNION
SELECT ...
FROM sotrudniki
WHERE sotrudniki.tab_num<>@otv
говорит о том, что фильтр-то и невозможен :)

Кстати, если стоит ORDER BY и алгорит различен, то
1) заменить UNION на UNION ALL
2) отакзаться от UNION, различия алгоритма поместить в case ... end

вместо Update @TabVesa проще сделать операции в селесте на первым запросом:

SELECT
isnull(sum(fct), 0) SumFct, ...
FROM (

) g

← →
И. Павел (2009-03-20 12:02) [18]

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

Можно ли это сделать, и, если можно, то как? В конечной таблице 60 полей, на каждое пользователь может наложить фильтр, выбрав операцию (=, >, <, <>) и значение (причем, в дальнейшем я буду писать другие виды фильтров: по диапазону, исключающие). Если передать в SQL-функцию фильтр через переменную типа Text, можно ли значение этой переменной использовать в предложении WHERE?

> как ты думаешь должно работать объединение если вместо одного
> раза в начале ему будут подсовывать рекордсет на каждую
> итерацию?

Я думал, что из базы записи выбираются так: для каждой записи в основной таблице, указанной в FROM выполняется первый JOIN, для каждой записи первого JOIN - 2-ой. Можно, ведь, написать так:
...
LEFT JOIN tab1 ON 1=1
LEFT JOIN tab2 ON tab2.id=tab1.id
...
Или же tab1 и tab2 сразу куда-то выгружаются, и потом уже оттуда идет их соединение. Если так, то в SQL функциях в JOIN вообще нельзя использовать меняющиеся аргументы.

> надеюсь через WHERE, а не локальный фильтр

Фильтр в Where

> ЮЮ ©

Спасибо за советы и варианты оптимизации. Попробую пока написать скалярные функции отдельно для ych_fct и ych_pl.

Вызов SQL-функции с меняющимся аргументом Найти похожие ветки