Переменное число критериев отбора в запросе

[irq]

Здравствуйте! Необходимо написать процедуру для выборки данных, в которой некоторые критерии отбора не принимались бы во внимание, если пользователь не ввел для них значения. Наверняка кто-то сталкивался с такой задачей. Какие использовали решения? Спасибо.

[Дмитрий]

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

[irq]

2 Дмитрий:
Хочется красивого и компактного решения. Как Вы себе представляете проверку "чего указано" при ... хотя бы 5... входящих параметрах?

[kdv]

Хочется красивого и компактного решения.

то есть, не хочется писать код?

пишется код, динамически подставляются условия where. на сервер уходит готовый запрос. красивость и компактность зависит от умения программировать на pascal, c++ и т.п.

[jake]

Здравствуйте! Необходимо написать процедуру для выборки данных, в которой некоторые критерии отбора не принимались бы во внимание, если пользователь не ввел для них значения. Наверняка кто-то сталкивался с такой задачей. Какие использовали решения? Спасибо.

Если критериев фиксированное кол-во и надо именно просто не принимать некоторые из них во внимание, если значение не указано, то можно сделать примерно так:

Код: Выделить всё

select ...
...
where (:Param1 is NULL or Field1=:Param1) and
      (:Param2 is NULL or Field2=:Param2) and
...

правда это чревато проблемами с использованием индексов. надо делать аккуратно и выбирать оптимальное для конкр. случая решение.

[irq]

то есть, не хочется писать код?

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

пишется код, динамически подставляются условия where. на сервер уходит готовый запрос. красивость и компактность зависит от умения программировать на pascal, c++ и т.п.

а как же оптимизация? планов-то в этом случае не будет

[irq]

2 jake:
Спасибо за решение. Если можно вкратце о том, какие проблемы с индексами здесь могут возникнуть?

[jake]

2 jake:
Спасибо за решение. Если можно вкратце о том, какие проблемы с индексами здесь могут возникнуть?

Они просто не будут использоваться для условий Field=:Param, если есть еще условие объединенное по OR

Не знаю, поправят ли что-то по этому поводу в FB2?...

[irq]

Они просто не будут использоваться для условий Field=:Param, если есть еще условие объединенное по OR

Не знаю, поправят ли что-то по этому поводу в FB2?...

Спасибо. При текущей постановке задачи это решение подходящее

[kdv]

если условий отбора совсем нет, то можно ставить where 1 = 1. то есть базовый код именно такой, а условие 1 = 1 будет "забиваться" условиями отбора полей. тогда не надо мудрить с кодом определения, введено хоть одно условие отбора, или нет.

[kdv]

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

в смысле - в sql этот вопрос решаем одним движением пальца? оригинально. задача состоит в динамическом формировании запроса, ничего здесь страшного нет и тем более нет никакого "кода размером в несколько страниц".

а как же оптимизация? планов-то в этом случае не будет

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

кстати, пример jake - (:Param1 is NULL or Field1=:Param1) не рекомендую. тут вообще ни о каких индексах не может быть и речи, и при условиях отбора например с 5-ти и опр. количестве записей будут просто тормоза, и ничего более. поэтому настоятельно советую именно динамическое формирование клиентом текста where. есть условие - добавляем к where. нет условия - не добавляем.

[jake]

кстати, пример jake - (:Param1 is NULL or Field1=:Param1) не рекомендую. тут вообще ни о каких индексах не может быть и речи, и при условиях отбора например с 5-ти и опр. количестве записей будут просто тормоза, и ничего более. поэтому настоятельно советую именно динамическое формирование клиентом текста where. есть условие - добавляем к where. нет условия - не добавляем.

Да, про проблемы с использованием индексов я тоже написал. Но например в случае хр. процедуры, зачастую, это может оказаться лучшим решением чем
select ... from StoredProc where....
при разумном использовании (:Param1 is NULL or Field1=:Param1) внутри процедуры конечно.
Кстати в MSSQL при таком "финте" с OR индексы все равно используются, когда это нужно. Дмитрий, Вы не в курсе, эта проблема будет решена в FB2? Или внутренняя архитектура IB/FB этого вообще не позволяет?

[kdv]

(:Param1 is NULL or Field1=:Param1)

если :param1 is null, то индекс не нужен, наоборот, он будет мешать.
если :param1 is not null, то индекс нужен.

кроме того, условие :param1 is null не по столбцу. конечно, если предположить, что данное условие с or используется исключительно для таких вот "выкрутасов", то теоретически можно ковырнуть оптимизатор, чтобы он игнорировал часть ":Param1 is NULL or".

как там будет с этим в FB2, пока не знаю.

[dimitr]

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

[Сергей]

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

В Firebird 1.5 есть такая конструкция:

EXECUTE STATEMENT <string>;

FOR EXECUTE STATEMENT <string> INTO :var1, ., :varn DO
<compound-statement>;

Sample:

CREATE PROCEDURE DynamicSampleThree (TextField VARCHAR(100), TableName VARCHAR(100))
RETURNING_VALUES (Line VARCHAR(32000))
AS
DECLARE VARIABLE OneLine VARCHAR(100);

BEGIN
Line = '';
FOR EXECUTE STATEMENT 'SELECT ' || TextField || ' FROM ' || TableName
INTO :OneLine
DO
IF (OneLine IS NOT NULL) THEN
Line = Line || OneLine || ' ';
SUSPEND;
END