Упрямые DataSource не дают построить прайс-лист

[Яга1]

Помогите пож-та, у меня есть таблица номенклатур на которую очень редко и вразнобой меняется цена, поэтому решили хранить не прейкурант полностью, а таблицу, что цена на такой-то ItemId меняется с такой-то даты на такую-то (иногда на 0). Неожиданно для себя не удалось справиться с проблемой, как на заданную дату вывести полный прайс номенклатур с ненулевыми ценами на форму.
Сперва думали обойтись одним датасорсом, но никак не удается заставить его выводить только ОДНУ последнюю строку измененной цены, выводятся и все предыдущие. Тогда решили действовать двумя датасорсами (таблица одна и та же)
Первый датасорс выводит на форму ItemId и дату последнего изменения цены на эту номенклатуру
public void init()
{
QueryBuildDatasource qbds;
;
super();

qbds = this.query().dataSourceTable(tableNum(SOPCenaTable));
qbds.addGroupByField(fieldNum(SOPCenaTable, ItemId));
qbds.addOrderByField(fieldNum(SOPCenaTable, ItemId),SortOrder::Ascending);
qbds.addOrderByField(fieldNum(SOPCenaTable, TransDate),SortOrder:: Descending);
qbds.addSelectionField(fieldNum(SOPCenaTable, TransDate), SelectionField::Max);
qbds.addSelectionField(fieldNum(SOPCenaTable, ItemId), SelectionField:: Database);

qbrCenaTransDate=qbds.addRange(fieldNum(SOPCenaTable, TransDate)); //будет ограничен датой поиска

return;
}
А второй датасорс, который должен отобразить цену и остальные подробности, никак не приделать. Если его присоединять без GroupBy, то почему-то вся информация второго датасорса выводятся как пустая.
Попробовали с GroupBy
public void init()
{
QueryBuildDatasource qbds;
;
super();

qbds = this.query().dataSourceTable(tableNum(SOPCenaTableView));
qbds.addGroupByField(fieldNum(SOPCenaTableView, ItemId));
qbds.addGroupByField(fieldNum(SOPCenaTableView, TransDate));

qbds.addSelectionField(fieldNum(SOPCenaTableView, TransDate), SelectionField::Max);
qbds.addSelectionField(fieldNum(SOPCenaTableView, ItemId), SelectionField::Max);
qbds.addSelectionField(fieldNum(SOPCenaTableView, Price), SelectionField::Max);

qbds.addLink(fieldNum(SOPCenaTableView, ItemId),fieldNum(SOPCenaTable, ItemId));
qbds.addLink(fieldNum(SOPCenaTableView, TransDate),fieldNum(SOPCenaTable, TransDate));
//здесь еще хотели добавить проверку, что цена ненулевая
}
Тип связи InnerJoin, чтобы фильтровать ненулевые цены. Но Аксапта не хочет взять только те строки, для которых в первом датасорсе уже найден TransDate, а подтягивает и все предыдущие даты с ценами, а если поглядеть на запрос, так он получается невообразимый, вторая таблица попала в условие Group bу вместе с первой
SELECT FIRSTFAST MAX(TransDate), ItemId FROM SOPCenaTable GROUP BY SOPCenaTable.ItemId, SOPCenaTableView.ItemId, SOPCenaTableView.TransDate ORDER BY SOPCenaTable.ItemId ASC, SOPCenaTable.TransDate DESC WHERE ((Department = N'ВП-52-29')) AND ((TransDate<={ts '2017-01-27 00:00:00.000'})) JOIN FIRSTFAST MAX(TransDate), MAX(ItemIdMAX(Price) FROM SOPCenaTableView WHERE SOPCenaTable.ItemId = SOPCenaTableView.ItemId AND SOPCenaTable.TransDate = SOPCenaTableView.TransDate


при этом при всем в SQL Management Studio запросы построили легко и правильно выдавали прейскурант (здесь немного по-другому названы таблицы)
select *
from Invent
inner join (select Max(TransDate) As mtd, Item as mItem
from Price
WHERE (TransDate <= CONVERT(DATETIME, '2016-09-08 00:00:00', 102))
group by Item)
As td on Invent.Item=mItem

inner join Price as prend on (prend.Item=mItem) and (prend.TransDate=mtd)
where prend.Price >0
order by Invent.Item

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

Axapta 2009 MS SQL Server 2008

[bitter]

Можно предварительно собрать данные во временную таблицу и выводить ее в качестве датасорса.

[Wamr]

Вот так можно

SELECT *
FROM MyPrice P1
WHERE P1.TransDate <= []
AND NOT EXISTS(SELECT 1 FROM MyPrice P2 
WHERE P2.ItemId = P1.ItemId AND P2.TransDate <= [] AND P2.TransDate > P1.TransDate)

добавить ограничения по цене или еще какие на своё усмотрение

[Яга1]

Пока не понятно. Что такое 1: AND NOT EXISTS(SELECT 1 FROM MyPrice P2
И ведь именно что существуют изменения цен и до и после заданной даты _DATE
Так-то в X++ для любого _ItemId написать бы функцию
SELECT FIRSTONLY MyPrice1
ORDER BY ItemId, TransDate DESC
WHERE MyPrice1.ItemId==_ItemId && MyPrice1.Transdate<=_DATE
потом по цене отфильтровать
Но как бы сделать такой поиск в датасорсе на форме. Даже если Ваш select верный, на форму-то как его засунуть

[Владимир Максимов]

Вам же Wamr привел пример. Этот запрос можно реализовать в Query. Не пробовали запускать в SQL? Будет возвращать то, что нужно, даже если есть цены и после _DAET

Может, в таком виде будет нагляднее

SELECT *
FROM MyPrice P1
WHERE P1.TransDate <= _DATE
      AND NOT EXISTS(SELECT 1 FROM MyPrice P2 
                     WHERE P2.ItemId        = P1.ItemId 
                           AND P2.TransDate <= _DATE 
                           AND P2.TransDate > P1.TransDate)

1 - это константа. Число 1 Надо понимать буквально. Для подзапроса Exists не имеет значения, что именно он вернет. Важен сам факт наличия записи, а не ее содержимое. Поэтому и ставят константу. Хотя "ветераны программирования" предпочитают символ "x" по привычке. Вероятно, потому, что 1 символ - это 1 байт (ну, или 2 для UNICODE). А одно число - это 4 байта. Привычка экономить на всем

[Яга1]

Кажется дошло! Спасибо большое, пойду пробовать

[dech]

Цитата:

Сообщение от Владимир Максимов

Вероятно, потому, что 1 символ - это 1 байт (ну, или 2 для UNICODE). А одно число - это 4 байта. Привычка экономить на всем

А как же ноль в конце строки? для юникода будут те же 4 байта. :-)
А с числами обычно быстрее работается. Но в данном случае разница в пару тактов процессора.

[Яга1]

Здравствуйте! Запрос безусловно правильный, но не удается дойти до победного
В форме в executeQuery получается запрос
SELECT FIRSTFAST * FROM SOPCenaTable
ORDER BY SOPCenaTable.ItemId ASC, SOPCenaTable.TransDate DESC
WHERE ((TransDate<={ts '2016-09-01 00:00:00.000'}))
NOTEXISTS JOIN FIRSTFAST * FROM SOPCenaTableView
WHERE SOPCenaTable.ItemId = SOPCenaTableView.ItemId
AND ((((SOPCenaTable.TransDate<SOPCenaTableView.TransDate))
AND ((SOPCenaTableView.TransDate<={ts '2016-09-01 00:00:00.000'}))))

Проблема с AND ((SOPCenaTableView.TransDate<={ts '2016-09-01 00:00:00.000'}))))
это условие не отрабатывает
Первое условие ((TransDate<={ts '2016-09-01 00:00:00.000'})) я строю как
qbrCena1TransDate.value( date2str(datenull(),123,2,2,2,2,4)+ ".." +date2str(_DATE,123,2,2,2,2,4) ); и оно работает нормально
А второе как qbrCena2TransDate.value('((SOPCenaTable.TransDate<SOPCenaTableView.TransDate)) AND ((SOPCenaTableView.TransDate<='
+sqlSystem.sqlLiteral(_DATE)
+'))');
и датовая константа в запросе получается вроде похожа на правильную, но фильтрация по ней не происходит. Проверяли, заменяя NotExists Join на Inner Join
Учитываются строки с более поздними датами

Может быть подскажете как справиться с этой проблемой?
Возможно дело в скобочках?

[dech]

qbrCena1TransDate.value(queryRange(dateNull(), SOPCenaDoc.TransDate));

qbrCena2TransDate.value(queryRange(SOPCenaTable.TransDate + 1, dateNull()));
qbrCena2TransDate.value(queryRange(dateNull(), SOPCenaDoc.TransDate));

[Wamr]

qbrCena2TransDate.value('((SOPCenaTable.TransDate<SOPCenaTableView.TransDate) && (SOPCenaTableView.TransDate<='+date2StrXpp(_DATE)+'))');

[Яга1]

1)qbrCena2TransDate.value(queryRange(dateNull(), SOPCenaDoc.TransDate)) вообще убивает предыдущее условие, остается только ((TransDate<={ts '2017-01-27 00:00:00.000'})), а надо AND


2)qbrCena2TransDate.value(queryRange(SOPCenaTable.TransDate + 1, dateNull()));
в запросе превращается в ((TransDate>={ts '1900-01-02 00:00:00.000'}))

[Яга1]

qbrCena2TransDate.value('((SOPCenaTable.TransDate<SOPCenaTableView.TransDate) && (SOPCenaTableView.TransDate<='+date2StrXpp(_DATE)+'))');
в запросе превратилось в
((((SOPCenaTable.TransDate<SOPCenaTableView.TransDate) && (SOPCenaTableView.TransDate<=01\09\2016))))
здесь AND написано не так и дата тоже, но условие почему-то сработало
это не случайность? огромное спасибо!

[dech]

Цитата:

Сообщение от Яга1

1)qbrCena2TransDate.value(queryRange(dateNull(), SOPCenaDoc.TransDate)) вообще убивает предыдущее условие, остается только ((TransDate<={ts '2017-01-27 00:00:00.000'})), а надо AND


2)qbrCena2TransDate.value(queryRange(SOPCenaTable.TransDate + 1, dateNull()));
в запросе превращается в ((TransDate>={ts '1900-01-02 00:00:00.000'}))

Надо просто новый QueryBuildRange добавить

qbrCena2TransDate.value('(SOPCenaTable.TransDate < SOPCenaTableView.TransDate)')
qbrCenaTableTransDate = qbds.addRange(fieldNum(SOPCenaTableView, TransDate));
qbrCenaTableTransDate.value(queryRange(datenull(), _DATE));

где qbds - ваш датасорс SOPCenaTableView

[Яга1]

Тогда условия связались по OR:
(((SOPCenaTable.TransDate < SOPCenaTableView.TransDate)) OR (TransDate<={ts '2016-09-01 00:00:00.000'}))

[AlGol]

Цитата:

решили хранить не прейкурант полностью, а таблицу, что цена на такой-то ItemId меняется с такой-то даты на такую-то (иногда на 0).

Может проще будет доработать таблицу с ценами до полноценной реализации модели ValidFrom-ValidTo?
Добавить поле даты окончания срока действия цены ValidTo, заполнять его по умолчанию максимальной датой, а при создании строки с последующей датой заполнять его соответстующим значением окончания срока действия.

Тогда запросы по актуальным ценам упростятся до
[дата цены]>=ValidFrom AND [дата цены] <= ValidTo.

[KiselevSA]

Цитата:

Сообщение от AlGol

Может проще будет доработать таблицу с ценами до полноценной реализации модели ValidFrom-ValidTo?
.

C ценами нельзя использовать такую модель, т.к. полноценная реализация запрещает иметь пересекающиеся диапазоны. В реально жизни очень часто встречаются маркетинговые акции, действующие короткий срок и с ценами ниже обычной. После окончания маркетинговой акции опять создавать нормальные цены? В свою бытность, помню, даже мутили специальную модификацию, которая выполняла последовательное создание ценового соглашения, если в журнале цен у записи была указана дата окончания действия: закрыть предыдущую датой начала и создать копию с датой начала равной дате окончания + 1.

[dn]

Цитата:

Сообщение от KiselevSA

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

Это решается. У нас, например, сделан отдельный тип ЖКС Акция. При его разноске дополнительно действующая цена закрывается за день до начала акции + создается копия этой цены с датой начала на день позже даты окончания акции.

[AlGol]

Цитата:

Сообщение от KiselevSA

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

Да, подобные алгоритмы и подразумевают нормальную непротиворечивую реализацию модели ValidFrom-ValidTo (или Date Effective Framework как это названо в AX2012).

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

[Яга1]

Цитата:

Сообщение от AlGol

Может проще будет доработать таблицу с ценами до полноценной реализации модели ValidFrom-ValidTo?
Добавить поле даты окончания срока действия цены ValidTo, заполнять его по умолчанию максимальной датой, а при создании строки с последующей датой заполнять его соответстующим значением окончания срока действия.

Тогда запросы по актуальным ценам упростятся до
[дата цены]>=ValidFrom AND [дата цены] <= ValidTo.

А также нужно возвращать в ValidTо максимальную дату, если новая строка цен удалилась
И не максимальную, а какую-то там еще, если удалилась не последняя строка
И при изменении ValidFrom, если в результате строка перескакивает через несколько строк назад, тоже исправить ValidTo.
Мы уже делали так. Впечатление, что ПРОЩЕ все-таки qbrCena2TransDate.value('((SOPCenaTable.TransDate<SOPCenaTableView.TransDate) && (SOPCenaTableView.TransDate<='+date2StrXpp(_DATE)+'))'); как предложил Wamr
Наглядно и точно нет ошибок при пересчете ValidTo

[Владимир Максимов]

Цитата:

Сообщение от Яга1

Впечатление, что ПРОЩЕ все-таки

Есть два связанных между собой процесса

1. Запись и модификация данных
2. Выборка данных для анализа

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

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

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

И тут вопрос в том, что для Вас важнее? Что чаще придется делать? Ведь, в конце концов, написать код триггерров на insert/update/delete придется только один раз. Пусть даже он будет достаточно сложным. А вот выборки цен Вы будете делать еще много раз и по разным поводам. И каждый раз Вы будете мучительно долго вспоминать, как же это все "упихать" в один запрос, причем в синтаксисе Axapta.