Имеется таблица с историей изменения документов. Поля таблицы: POL_ID (ID документа), POL_STA_ID (статус документа: -1 закрыт,0 приостановлен,1 активен), POL_JN_OPERATION (операция 1-добавлен,0 изменен,-1 удален,2 изменен статус), POL_JN_DATE_WRITE (TIMESTAMP операции) ну и прочие реквизиты. При изменении документа в историю пишется 2 (если меняется статус) либо 0 (если другие реквизиты). Активность документов может быть на время приостановлена (причем несколько раз). Нужно вытащить из нее документы в виде: ID-документа, Дата нач. действия, Дата окончания действия (т.е. промежутки времени в которых документ был активен). DB2 v.9.1.2 Win (Express-C). Делаю следующее:

SELECT TBL1.POL_ID,TBL1.POL_JN_DATE AS D_START,TBL2.POL_JN_DATE AS D_END
FROM
(SELECT POL_ID,POL_JN_DATE_WRITE FROM USRSCHEMA.HISTORY
WHERE POL_STA_ID=1 AND (POL_JN_OPERATION=1 OR POL_JN_OPERATION=2))
AS TBL1(POL_ID,POL_PEO_ID,POL_JN_DATE)
LEFT JOIN
(SELECT POL_ID,POL_JN_DATE_WRITE FROM USRSCHEMA.HISTORY
WHERE (POL_STA_ID=0 OR POL_STA_ID=-1) AND POL_JN_OPERATION=2)
AS TBL2(POL_ID,POL_PEO_ID,POL_JN_DATE)
ON TBL2.POL_ID=TBL1.POL_ID
WHERE
TBL2.POL_JN_DATE=(SELECT MIN(POL_JN_DATE_WRITE) FROM USRSCHEMA.HISTORY
WHERE (POL_STA_ID=0 OR POL_STA_ID=-1) AND POL_JN_OPERATION=2 AND
POL_JN_DATE_WRITE>TBL1.POL_JN_DATE)
OR TBL2.POL_JN_DATE IS NULL

Работает некорректно, такое впечатление, что не работает (POL_STA_ID=0 OR POL_STA_ID=-1) AND... во внешнем WHERE. Меняю WHERE во внешнем запросе - разделив его на два отдельных:

SELECT TBL1.POL_ID,TBL1.POL_JN_DATE AS D_START,TBL2.POL_JN_DATE AS D_END
FROM
(SELECT POL_ID,POL_JN_DATE_WRITE FROM USRSCHEMA.HISTORY
WHERE POL_STA_ID=1 AND (POL_JN_OPERATION=1 OR POL_JN_OPERATION=2))
AS TBL1(POL_ID,POL_PEO_ID,POL_JN_DATE)
LEFT JOIN
(SELECT POL_ID,POL_JN_DATE_WRITE FROM USRSCHEMA.HISTORY
WHERE (POL_STA_ID=0 OR POL_STA_ID=-1) AND POL_JN_OPERATION=2)
AS TBL2(POL_ID,POL_PEO_ID,POL_JN_DATE)
ON TBL2.POL_ID=TBL1.POL_ID
WHERE
TBL2.POL_JN_DATE=(SELECT MIN(POL_JN_DATE_WRITE) FROM USRSCHEMA.HISTORY
WHERE POL_STA_ID=0 AND POL_JN_OPERATION=2 AND
POL_JN_DATE_WRITE>TBL1.POL_JN_DATE)
OR
TBL2.POL_JN_DATE=(SELECT MIN(POL_JN_DATE_WRITE) FROM USRSCHEMA.HISTORY
WHERE POL_STA_ID=-1 AND POL_JN_OPERATION=2 AND
POL_JN_DATE_WRITE>TBL1.POL_JN_DATE
OR
TBL2.POL_JN_DATE IS NULL

Работает корректно. Пз, натолкните на ответ - Почему? Чего я не вижу? На мой взгляд оба запроса эквивалентны.

BuryCommonerПо-моему во втором запросе не хватает закрывающей скобки
Да, удалял лишнию информацию из копи/паста и снес случайно. Но это действительно не важно.

BuryCommoner
в любом случае вернёт одно значение, ведь выбираете MIN(). Следовательно во втором случае два подзапроса вернут два значения.
Дык в том-то и дело что нужен ОДИН результат MIN(). Если есть в таблице две записи, в которых: в одной СТАТУС=-1, а в другой СТАТУС=0, подзапрос с MIN() выводит результат только для второй, а первую не видит в упор. Иными словами:
(XXX or YYY) and ... работает как (YYYY) and...
Т.е. если есть запись с POL_STA_ID=-1 (и с данным ID нет записей с POL_STA_ID=0) то

SELECT MIN(POL_JN_DATE_WRITE) FROM USRSCHEMA.POLICIES_JN
WHERE (POL_STA_ID=0 OR POL_STA_ID=-1) AND POL_JN_OPERATION=2 AND POL_JN_DATE_WRITE>TIMESTAMP('2009-09-08','00.00.00')

ничего не возвращает, а если наоборот - все нормально.

К предыдущему собственному посту.

Отдельно

SELECT MIN(POL_JN_DATE_WRITE) FROM USRSCHEMA.POLICIES_JN
WHERE (POL_STA_ID=0 OR POL_STA_ID=-1) AND POL_JN_OPERATION=2 AND POL_JN_DATE_WRITE>TIMESTAMP('2009-09-08','00.00.00')

работает как и ожидается, но вот в весь запрос (с JOIN-ом) нет

BuryCommonerСтранно было бы ждать другого.

? В JOIN-е то для конкретной записи есть только ОДИН вариант (СТАТУС=-1), почему его нет в результате? По логике, тогда не должно быть и результата для варианта с СТАТУС=0.
Возможно я не вполне ясно объяснил?

Есть записи для 4-х документов (ID, СТАТУС, ОПЕРАЦИЯ, TIMESTAMP):
1, 1, 1, 2009-08-08-00.00.00 -- добавлена запись в историю
1, -1, 2, 2009-08-09-02.00.00 -- изменен статус (закрыт)
2, 1, 1, 2009-08-08-00.00.00 -- добавлена запись в историю
2, 0, 2, 2009-08-09-01.00.00 -- изменен статус (приостановлен)
3, 1, 1, 2009-08-08-00.00.00 -- добавлена запись в историю
3, 0, 2, 2009-08-09-03.00.00 -- изменен статус (приостановлен)
3, 1, 2, 2009-08-09-05.00.00 -- изменен статус (восстановлен)
3, -1, 2, 2009-08-09-06.00.00 -- изменен статус (закрыт)
4, 1, 1, 2009-08-08-00.00.00 -- добавлена запись в историю

При этом первый вариант запроса возвращает результат в который включены записи с ID=2,3,4 но нет записи с ID=1 примерно такой (не пишу полностью дамп дабы не загромождать):
2 2009-08-08-00.00.00 2009-08-09-01.00.00
3 2009-08-08-00.00.00 2009-08-09-03.00.00
3 2009-08-09-05.00.00 2009-08-09-06.00.00
4 2009-08-08-00.00.00 -

второй вариант запроса возвращает результат для всех записей:
1 2009-08-08-00.00.00 2009-08-09-02.00.00
2 2009-08-08-00.00.00 2009-08-09-01.00.00
3 2009-08-08-00.00.00 2009-08-09-03.00.00
3 2009-08-09-05.00.00 2009-08-09-06.00.00
4 2009-08-08-00.00.00 -

[quot BuryCommoner]Действительно странно. Пересобрать статистику пробовали? Может оптимизатор отбрасывает, по его мнению излишние условия, из-за чего и получается вместо (xxx OR yyy) and zzz - yyy and zzz.

Статистика тут ни при чем т.к. во-первых менять логику запроса в зависимости от статистики это даже для сурового DB2-ного оптимизатора перебор, а во-вторых прежде чем создать топик еще раз все это воссоздал в отдельной таблице - результат тот же.
Сильно подозреваю, что таки где-то в логике запроса. Если "на пальцах", то я вижу запросы примерно так:

в первом запросе:
Запрос из (Множество А + Множество В)
Включить в результат:
если В1=Функция(без разницы Х или Y)

во втором запросе:
Запрос из (Множество А + Множество В)
Включить в результат:
если В1=Функция(Х) или В1=Функция(Y)

При этом в каждой записи из любого множества может быть только либо Х либо Y.
Может, не вполне удачно "нарисовал", но я старался :)
Так вот на мой взгляд оба запроса одинаковы, на взгляд DB2 - нет. Чего я упустил? Вначале все-таки выполняется JOIN, а затем внешний WHERE или не так?

Извиняюсь за задержку - Инет только на работе.

BuryCommoner У вас же в первом запросе в глобальном условии WHERE в подзапросе минимальная дата берётся со всей таблицы без привязки к конкретному документу.
Т.е. POL_JN_DATE_WRITE>TBL1.POL_JN_DATE не катит? Этой конструкцией я как раз и пытался сделать привязку.

BuryCommoner
Попробуйте такой запрос:

Работает, проверил на реальных данных. Большое спасибо.

askfinder Этой конструкцией я как раз и пытался сделать привязку.
Еще раз проанализировал и вопросов меньше не стало. Действительно, минимальная берется со всей таблице (что собссно без разницы для бизнес-логики - нужно только одно значение) но ведь:
1. POL_JN_DATE_WRITE>TBL1.POL_JN_DATE разве не говорит что минимальную дату нужно искать по всей истории для записей именно из TBL1?
2. Если нет, то каким образом в результат попадают именно правильные значения, а не "с потолка"?
3. Если да, то почему такая "избирательность" отбора записей?
Если для документа есть записи где СТАТУС=0 и СТАТУС=-1 (да любое другое их сочетание), все работает корректно, если есть только СТАТУС=-1 - фигня получается. Вот это больше всего и не понятно.

BuryCommonerПричем минимальное POL_JN_DATE_WRITE берется для ВСЕХ документов, а не только для тех у которых POL_ID совпадает с соединенными подзапросами.
Хм. А как быть с POL_JN_DATE_WRITE> TBL1.POL_JN_DATE ? Кроме того
BuryCommonerне во всех случаях значение MIN(POL_JN_DATE_WRITE) выбранное из ВСЕХ документов, будет равно POL_JN_DATE_WRITE документа в ткущей строке.
было бы для РАЗНЫХ записей а не только для определенного типа. Если взять большее число записей, то закономерность четко прослеживается. Для меня во всяком случае, все это весьма не очевидно.
В принципе, решение проблемы уже было, теперь есть еще одно (Ваше более оптимально по крайней мере с точки зрения затрат), просто хотелось понять логику.