Некоторые аспекты использования пользовательских функций в предложениях SQL.

"Старый конь борозды не испортит". "В чем преимущество склероза ?. В том, что все время узнаешь новое"

Уже надоело говорить о том, что ровным счетом ничего нового в этой жизни не происходит, а большинство нового - это хорошо забытое старое. Вот еще пример. Один автор этой статьи пожилой , но относительно молодой фоксист , начавший работать только с FXP2.6 под виндами, а второй -один из старейших клиперистов. Поэтому и приемы разные. У первого в основном новые методы ФОКСА, у второго вечные истины dbase. Оказывается, что старые рецепты часто дают много лучшие решения, чем новомодные навороты, зная которые , очень трудно использовать старые приемы, хотя и читал теорию и потенциально их знаешь. Потом, черт возьми, вечно попадаешь на агрессивную рекламу Микрософт с описанием новых, все более мощных методов. И часто веришь всему, что они говорят. Но вот берем тривиальные задачи, суем их на новую кухню и ждем вкусного пирога. А вместо него иногда - одна вонь.

Речь пойдет в основном о SQL select с предложениями outer left/right join, union и использовании групповых функций.

В одной из предыдущих статей мы уже упоминали о том, что генератор запросов и представлений view дает неправильный код с этими предложениями, если имеем несколько join.

Пример такого ошибочного кода:

LEFT OUTER JOIN dbf_s!valuta; 
ON int(pd.valuta_id) == int(Valuta.valuta_id) ; 
ON int(pd.kv_izg) == int(Valuta_a.valuta_id) ; 
ON pd.shpz_id = Sc_shpz.shpz_id ; 
ON pd.nc_id = bc_ac.nc_id ; 
ON pd.country_id = country.country_id ; 
ON pd.um_id = Sc_ed.um_id ; 
LEFT OUTER JOIN dbf_s!valuta Valuta_a ; 
LEFT OUTER JOIN dbf_s!Sc_shpz; 
LEFT OUTER JOIN dbf_s!bc_ac; 
LEFT OUTER JOIN dbf_s!Country ; 
LEFT OUTER JOIN dbf_s!Sc_ed 
А нужно- 
LEFT OUTER JOIN dbf_s!valuta; 
ON int(pd.valuta_id) == int(Valuta.valuta_id) ; 
LEFT OUTER JOIN dbf_s!valuta Valuta_a ; 
ON int(pd.kv_izg) == int(Valuta_a.valuta_id) ; 
LEFT OUTER JOIN dbf_s!Sc_shpz; 
ON pd.shpz_id = Sc_shpz.shpz_id ; 
LEFT OUTER JOIN dbf_s!bc_ac; 
ON pd.nc_id = bc_ac.nc_id ; 
LEFT OUTER JOIN dbf_s!Country ; 
ON pd.country_id = country.country_id ; 
LEFT OUTER JOIN dbf_s!Sc_ed ; 
ON pd.um_id = Sc_ed.um_id 

Ну это не беда. "Кто предупрежден - тот вооружен".

Беда в другом - генератор позволяет к этому добавить предложение where, где можно использовать любые из уже использованных таблиц.

Вот тут-то (особенно если условие where в нем касается присоединенных таблиц) результат может быть и вовсе неверен. А как такое условие не добавить, если нужно взять товар за определенный интервал времени? Или цену товара из прайс листа, соответствующую нужному учетному периоду? Этого в join никак не засунешь.

Без outer join, казалось бы, тоже нельзя - ссылка на любой справочник может быть пустой, а строчку товара терять нежелательно. Поэтому условия из outer join не получится перенести в where . Ждать, когда Микрософт исправит ошибки - так наши клиенты ждать нас не будут и быстренько слиняют к более удачливым фирмам или программным продуктам. Поэтому, хочешь не хочешь, а давай правильный результат.

Сначала мы разбили запрос на два -один с outer join , второй с where. Работает весьма неплохо и при наличии нужных индексов достаточно быстро. Однако, иногда получается надо писать уж слишком много веток по разным условиям. Число SQL и их сложность растет и растет. Более того, приходится включать вторичные справочники с еще одним outer join на таблицу, которая сама уже висит на outer join. Вероятность ошибки в результате слишком велика.

Вот тут то на помощь приходят старые, но от этого, не менее эффективные методы. А именно: использование пользовательских функций. Идея состоит в том, чтобы убрать из предложения Select SQL все таблицы, которые привязаны к внешним объединениям, а искать нужные ссылки в этих таблицах в пользовательских функциях. Функция должна быть такая, что при наличии ссылки - получить ее значение, а при отсутствии вернуть "пустышку" нужного типа. То есть сделать работу outer join самим. Что для этого нужно: имя ключа, по которому в справочнике ищется справка, имя справочной таблицы, поле из справочника, имя тэга, по которому ведется поиск.

Некоторые хитрости.

Положим, что для одного ключа из справочника должно быть найдено несколько разных полей. Не хочется перегружать работу поиска второй раз. Если нужная запись в справочнике найдена - для другого поля ни к чему заново делать поиск.

Второе.

SQL select обладает одним неприятным свойством: длина результирующего поля оценивается во время инициализации. Поэтому, если для первой записи справка отсутствует, а для следующей справка есть, то и для пустой записи нужно вернуть поле нужной длины. Иначе записи из справочника будут урезаны по одной букве.

Вот пример такой функции

Lparameters pr_id,mydat 
Local retcr 

If order('val_course')!='datcur' 
Set order to datcur in val_course 
Endif 
retcr=0 
If empty(mydat) 
set exact off 
If seek(str(pr_id,3),'val_course') 
retcr=course 
Endif 
Else 
If set('NEAR')='OFF' 
Set near on 
Endif 
=seek(str(pr_id,3)+dtos(mydat),'val_course') 
If pr_id=val_course.valuta_id 
retcr=val_course.course 
Endif 
Set near off 
Endif 
Return retcr: 

Function getref 
Lparameters fld,pr_id,tb,tg 

Local fl,tpt,rt,rtt,lnn, fl,fll 

fl=tb+'.'+alltrim(fld) 
fll=tb+'.'+tg 
*** Если совпал ключ, можно не искать, иначе - ищем 
If not empty(pr_id) and ; 
(eval(fll)=pr_id or seek(pr_id,tb,tg)) 
rtt=eval(fl) 
Return rtt 
Else 
rt=eval(fl) 
tpt=type('rt') 
Do case 
Case tpt='N' 
rtt=0 
Case tpt='C' 
Lnn=len(eval(fl)) 
rtt=pad('',lnn) 
Case tpt='D' 
rtt=ctod('..') 
Case tpt='L' 
rtt=.f. 
Endcase 
Return rtt 
Endif 

Lparameters pr_id,mydat 

Local retcr 

If order('val_course')!='datcur' 
Set order to datcur in val_course 
Endif 
retcr=0 
If empty(mydat) 
set exact off 
If seek(str(pr_id,3),'val_course') 
retcr=course 
Endif 
Else 
If set('NEAR')='OFF' 
Set near on 
Endif 
=seek(str(pr_id,3)+dtos(mydat),'val_course') 
If pr_id=val_course.valuta_id 
retcr=val_course.course 
Endif 
Set near off 
Endif 
Return retcr 

Пример использования. Пусть у нас был запрос SQL следующего вида:

Select doc.num,val from doc LEFT OUTER JOIN valuta; 
ON doc.valuta_id= Valuta.valuta_id 

Теперь пишем

Select doc.num getref('val','doc.valuta_id','valuta','valuta_id) as val from doc 

Здесь конечно получилось более громоздко, чем в исходном примере, зато можно добавлять еще кучу полей из справочников и кучу справочников и не боятся при этом использовать предложение where в этом же sql

Второй пример сложнее.

Есть таблица валют и таблица курсов валют с ключами валюты, датой и курсом. Причем в таблицу курсов валют курсы могут записываться не в порядке времени , а произвольно. Некоторые валюты в таблице курсов могут вообще отсутствовать. Требуется, тем не менее, показать все валюты с последним их курсом.

Решение первое (неправильное):

Select val,max(dat),cour from valuta, vcour where valuta.valuta_id=vcour.valuta_id union ;

Select val, ctod('..'), 0 as cour from valuta where valuta_id not in ( select valuta_id from vcour)

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

Решение в стиле Microsoft - правильное, но не оптимальное.

Select max(dat)as dt,valuta_id from ; 
vcour group by valuta_id into cursor qr 
Select distinct val,cour,dat from ; 
valuta,vcour,qr ; 
where valuta.valuta_id=vcour.valuta_id ; 
and qr.dt=vcour.dat and ; 
qr.valuta_id=valuta.valuta_id ; 
union ; 
Select distinct val,1 as cour,ctod('..') as dat from valuta where ; 
valuta.valuta_id not in (select distinct valuta_id from vcour); 
into cursor vcou 

Решение по дедовским заветам. Самое быстрое.

Создадим в таблице курса валют композитный индекс datcur с выражением 
STR(valuta_id,3)+DTOS(dat) 
Сделаем функцию Getvcr 
Lparameters pr_id,mydat 

Local retcr 

If order('vcour')!='datcur' 
Set order to datcur in vcour 
Endif 
retcr=0 
If empty(mydat) 
set exact off 
If seek(str(pr_id,3),'vcour') 
retcr=vcour.cour 
Endif 
Else 
If set('NEAR')='OFF' 
Set near on 
Endif 
=seek(str(pr_id,3)+dtos(mydat),'vcour') 
If pr_id=vcour.valuta_id 
retcr=vcour.cour 
Endif 
Set near off 
Endif 
Return retcr 

Теперь достаточно записать Select val,getvcr(valuta.valuta_id) as cour from valuta - и все.

А хотите курс на конкретную дату - укажите ее во втором параметре.

Так и вспоминается анекдот про двух бычков, старого и молодого. Молодой все норовил быстро побежать к коровкам. Нам же по нраву принцип старого бычка- пойдем медленно медленно, но поимеем все , что хотели и может даже обгоним кого и помоложе.