Query для 3-х таблиц с группировкой. - DAX: Программирование

user_ax · 24.05.2013, 15:55

Есть задача: построить отчёт на основании 3-х таблиц с определёнными условиями. Условия пока опустим. Нужно объединить 3 таблицы между собой с помощью query. Второй день бьюсь, как только не соединял - ничего не выходит.
Последняя версия такова:

X++:

Queryrun main_query(boolean _count_query = false)
{
    Query query   =      new Query();
    QueryBuildDataSource qbds_vendsplistjour,
                         qbds_vendsplisttrans,
                         qbds_vendinvoicetrans;
    QueryBuildRange      qbr;
    ;

    qbds_vendinvoicetrans  = query.addDataSource(tablenum(VendInvoiceTrans));
   qbds_vendinvoicetrans.addSelectionField(fieldnum(VendInvoiceTrans,Qty), SelectionField::Sum);
    qbds_vendinvoicetrans.orderMode(OrderMode::GroupBy);
    qbds_vendinvoicetrans.addSortField(fieldnum(VendInvoiceTrans,ItemId));
    qbds_vendinvoicetrans.addSortField(fieldnum(VendInvoiceTrans,InternalSp));
    qbds_vendinvoicetrans.addSortField(fieldnum(VendInvoiceTrans,Qty));

    qbds_vendsplisttrans  = qbds_vendinvoicetrans.addDataSource(tablenum(VendSpListTrans));
    qbds_vendsplisttrans.addLink(fieldnum(VendInvoiceTrans,InternalSp),fieldnum(VendSpListTrans,InternalSp));

    qbds_vendsplistjour = qbds_vendsplisttrans.addDataSource(tablenum(VendSpListJour));
    qbds_vendsplistjour.relations(true);

    if (_count_query)
    {
        qbds_vendinvoicetrans.addSelectionField(fieldnum(VendInvoiceTrans,Recid),SelectionField::Count);
    }

    info(qbds_vendinvoicetrans.toString());
    return (new QueryRun(query));
}

Он выдаёт такой sql запрос:

X++:

SELECT FIRSTFAST SUM(Qty), COUNT(RecId) FROM VendInvoiceTrans GROUP BY VendInvoiceTrans.ItemId ASC, VendInvoiceTrans.InternalSp ASC, VendInvoiceTrans.Qty ASC JOIN FIRSTFAST * FROM VendSpListTrans WHERE VendInvoiceTrans.InternalSp = VendSpListTrans.InternalSp JOIN FIRSTFAST * FROM VendSpListJour WHERE VendSpListTrans.InternalSp = VendSpListJour.InternalSp

который я подправил, чтобы проверить в sqlms

X++:

SELECT  SUM(VendInvoiceTrans.Qty), COUNT(VendInvoiceTrans.RecId) 
FROM VendInvoiceTrans 

  JOIN  VendSpListTrans 
  on VendInvoiceTrans.InternalSp = VendSpListTrans.InternalSp 
  JOIN VendSpListJour
  on VendSpListTrans.InternalSp = VendSpListJour.InternalSp
--GROUP BY VendInvoiceTrans.ItemId ,
-- VendInvoiceTrans.InternalSp

В sqlms он отрабатывает и выдаёт результаты.

Прошу помочь или подтолкнуть в нужную сторону, куда копать. Спасибо заранее.

С уважением.

Нашёл на форуме в теме такую фразу

Цитата:

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

В SQL при использовании GROUP BY нельзя получить данные, которые не входят в список агрегирующих полей (addSortField) или функций (SelectionField).
Так что Аксапта тут не виновата

Добавил нужные мне поля, которые хочу выбрать из данной таблицы - ничего(

ice · 24.05.2013, 16:03

зачем эта строка?

X++:

qbds_vendinvoicetrans.addSortField([COLOR=blue]fieldnum[/COLOR](VendInvoiceTrans,Qty));

user_ax · 24.05.2013, 16:15

Цитата:

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

зачем эта строка?

X++:

qbds_vendinvoicetrans.addSortField([COLOR=blue]fieldnum[/COLOR](VendInvoiceTrans,Qty));

Я процитировал человека выше, который сказал, что нельзя в group by получить данные, которые не входят в список полей, по которым делаются агрегирующие ф-ции.
Если её убрать - ничего не меняется.

S.Kuskov · 24.05.2013, 16:14

Цитата:

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

ничего не выходит.

это значит запрос возвращается пустым или что-то возвращает, но не то что хотелось бы?

Цитата:

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

X++:

qbds_vendinvoicetrans.addSortField(fieldnum(VendInvoiceTrans,Qty));

Эта строчка у вас для чего?
Как можно одновременно суммировать и группировать по одному и тому же полю?

Вы подчинённые таблицы цепляете только для фильтрации основной или для выборки из них каких-то полей?

user_ax · 24.05.2013, 16:17

Цитата:

Сообщение от S.Kuskov

это значит запрос возвращается пустым или что-то возвращает, но не то что хотелось бы?

Эта строчка у вас для чего?

Вы подчинённые таблицы цепляете только для фильтрации основной или для выборки из них каких-то полей?

Запрос возращается без нужных мне полей, выводится только поля из 1 таблицы и то вместо значений выводятся 0.

Цепляю для выборки из них полей.

ice · 24.05.2013, 16:22

Цитата:

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

Запрос возращается без нужных мне полей, выводится только поля из 1 таблицы и то вместо значений выводятся 0.

Цепляю для выборки из них полей.

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

user_ax · 24.05.2013, 16:23

Цитата:

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

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

То есть, мне нужно для двух остальных qbds прописать addSortField с теми полями, которые мне нужно вывести ?

S.Kuskov · 24.05.2013, 16:31

Цитата:

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

Цепляю для выборки из них полей.

Вы дважды "цепляете" поле Qty. Один раз в список агрегирующих функций

X++:

qbds_vendinvoicetrans.addSelectionField(fieldnum(VendInvoiceTrans,Qty), SelectionField::Sum);

а второй раз в список полей по которым идёт группировка

X++:

qbds_vendinvoicetrans.addSortField(fieldnum(VendInvoiceTrans,Qty));

Второе излишне. Более того оно искажает весь смысл группировки.

Вам по смыслу нужно складывать количество в разрезе каких-то групп полей? Каких?

user_ax · 24.05.2013, 16:33

Цитата:

Сообщение от S.Kuskov

Вы дважды "цепляете" поле Qty. Один раз в список агрегирующих функций

X++:

qbds_vendinvoicetrans.addSelectionField(fieldnum(VendInvoiceTrans,Qty), SelectionField::Sum);

а второй раз в список полей по которым идёт группировка

X++:

qbds_vendinvoicetrans.addSortField(fieldnum(VendInvoiceTrans,Qty));

Второе излишне. Более того оно искажает весь смысл группировки.

Вам по смыслу нужно складывать количество в разрезе каких-то групп полей? Каких?

Да, это я уже осознал и убрал второй раз поле qty.

Да, вы правы, мне нужно искать сумму поля qty в разрезе полей itemid && internalsp.

S.Kuskov · 24.05.2013, 16:40

Цитата:

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

Да, вы правы, мне нужно искать сумму поля qty в разрезе полей itemid && internalsp.

У вас поля InternalSp и ItemId находятся в той же таблице, что и qty. Зачем вам остальные таблицы. Скорее всего полей в группировке больше?

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

Можно иначе, но для этого нужно создавать промежуточные View.

user_ax · 24.05.2013, 16:32

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

user_ax · 24.05.2013, 16:47

Я вот думаю сделать это через While select что ли...

Нужно реализовать следующее:

Цитата:

Для отобранных строк из VendSpListJour и VendSpListTrans следует отбирать строки в VendInvoiceTrans (связь по полю VendSpListTrans.InternalSp), группировать поле Qty по ItemId и InternalSp, и выводить в виде отчёта, как описано ниже. Если в VendInvoiceTrans строк с соответствующим значением InternalSp не найдено, то соответствующая (правая) часть отчёта останется незаполненной.

maldini · 24.05.2013, 16:57

Странно! Каким способом ты получаешь данные из запроса.
Вот такой пример работает без проблем

X++:

Queryrun main_query(boolean _count_query = false)
{
    VendInvoiceJour     VendInvoiceJour;
    QueryRun            QueryRun;
    Query query   =      new Query();
    QueryBuildDataSource qbds;
    QueryBuildRange      qbr;
    ;
    qbds  = query.addDataSource(tablenum(VendInvoiceJour));
    qbds.addSortField(fieldnum(VendInvoiceJour,purchId));
    qbds.addSelectionField(fieldnum(VendInvoiceJour,Qty), SelectionField::Sum);
    qbds.orderMode(OrderMode::GroupBy);
    qbds  = qbds.addDataSource(tablenum(VendInvoiceTrans));
    qbds.addLink(fieldnum(VendInvoiceJour,purchId),fieldnum(VendInvoiceTrans,purchId));
    qbds.joinMode(JoinMode::InnerJoin);
    qbds = query.dataSourceTable(tableNum(VendInvoiceJour));

    QueryRun = new QueryRun(query);
    while (queryRun.next())
    {
        VendInvoiceJour = QueryRun.get(tablenum(VendInvoiceJour));
        info(strFmt("%1  %2",VendInvoiceJour.PurchId,VendInvoiceJour.Qty);
    }
}

user_ax · 24.05.2013, 17:02

Цитата:

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

Странно! Каким способом ты получаешь данные из запроса.
Вот такой пример работает без проблем

X++:

Queryrun main_query(boolean _count_query = false)
{
    VendInvoiceJour     VendInvoiceJour;
    QueryRun            QueryRun;
    Query query   =      new Query();
    QueryBuildDataSource qbds;
    QueryBuildRange      qbr;
    ;
    qbds  = query.addDataSource(tablenum(VendInvoiceJour));
    qbds.addSortField(fieldnum(VendInvoiceJour,purchId));
    qbds.addSelectionField(fieldnum(VendInvoiceJour,Qty), SelectionField::Sum);
    qbds.orderMode(OrderMode::GroupBy);
    qbds  = qbds.addDataSource(tablenum(VendInvoiceTrans));
    qbds.addLink(fieldnum(VendInvoiceJour,purchId),fieldnum(VendInvoiceTrans,purchId));
    qbds.joinMode(JoinMode::InnerJoin);
    qbds = query.dataSourceTable(tableNum(VendInvoiceJour));

    QueryRun = new QueryRun(query);
    while (queryRun.next())
    {
        VendInvoiceJour = QueryRun.get(tablenum(VendInvoiceJour));
        info(strFmt("%1  %2",VendInvoiceJour.PurchId,VendInvoiceJour.Qty);
    }
}

В вашем запросе вы к одному Qbds цепляете разные таблицы, а у меня к каждому свою.
Как всё-таки правильно?
У меня связи таблиц немного другие

ListJour => ListTrans - один ко многим
ListTrans => VendInvoiceTrans - много ко многим.
Может быть из-за этого проблема?

maldini · 24.05.2013, 17:07

Цитата:

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

В вашем запросе вы к одному Qbds цепляете разные таблицы, а у меня к каждому свою.
Как всё-таки правильно?
У меня связи таблиц немного другие

ListJour => ListTrans - один ко многим
ListTrans => VendInvoiceTrans - много ко многим.
Может быть из-за этого проблема?

QDBS тут совсем не причем. Всеравно все от query идет. Просто в вашем случае можно в любое место добавить какое либо условия, а в моем придется в переменную qdbs предварительно получить datasource того запроса куда буду добовлять фильтр, вот так

X++:

qdbs = query.dataSourceTable(tableNum(VendInvoiceJour));

Условия соединений я думаю тут не причем, если из моего примера убрать

X++:

qbds.joinMode(JoinMode::InnerJoin);

все так же будет работать проблема где то близко. )))

user_ax · 24.05.2013, 17:11

Цитата:

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

QDBS тут совсем не причем. Всеравно все от query идет. Просто в вашем случае можно в любое место добавить какое либо условия, а в моем придется в переменную qdbs предварительно получить datasource того запроса куда буду добовлять фильтр, вот так

X++:

qdbs = query.dataSourceTable(tableNum(VendInvoiceJour));

Сейчас попробую переписать по вашему примеру, спасибо. Посмотрим как будет.

user_ax · 24.05.2013, 17:26

Если джойнить 2 таблицы - всё замечательно, когда пытаюсь приджойнить третью - qbds валится в null.

X++:

Queryrun main_query(boolean _count_query = false)
{

    Query query   =      new Query();
    QueryBuildDataSource qbds;
   
    ;
    qbds  = query.addDataSource(tablenum(VendInvoiceTrans));
    qbds.addSortField(fieldnum(VendInvoiceTrans,ItemId));
    qbds.addSortField(fieldnum(VendInvoiceTrans,InternalSp));
    qbds.addSelectionField(fieldnum(VendInvoiceTrans,Qty), SelectionField::Sum);
    qbds.orderMode(OrderMode::GroupBy);
    qbds  = qbds.addDataSource(tablenum(VendSpListTrans));
    qbds.addLink(fieldnum(VendSpListTrans,InternalSp),fieldnum(VendInvoiceTrans,InternalSp));
    qbds.joinMode(JoinMode::InnerJoin);
    
    qbds = query.dataSourceTable(tableNum(VendSpListJour));
    qbds.addLink(fieldnum(VendSpListTrans,InternalSp),fieldnum(VendSpListJour,InternalSP));
    qbds.joinMode(JoinMode::InnerJoin);
    
    if (datefromstr && datetostr)
    {
        qbds.addRange(fieldnum(VendSpListJour,SpDate)).value(SysQuery::range(datefrom.value(),dateto.value()));
    }
    if (vendaccountstr)
    {
        qbds.addRange(fieldnum(VendSpListJour,OrderAccount)).value(SysQuery::value(vendaccountstr));
    }
     //если введён регистранионный номер договора
    if (rcontractaccountstr)
    {
        qbds.addRange(fieldnum(VendSpListJour, RcontractAccount)).value(SysQuery::value(rcontractaccount.value()));
    }
    //если введён Владелец
    if (emplidstr)
    {
        qbds.addRange(fieldnum(VendSpListJour, Emplid)).value(SysQuery::value(emplid.value()));
    }
    switch(spclosedstr)
    {
        case (AllOpenPosted::All):
           qbds.addRange(fieldnum(VendSpListJour,SpClosed));
        break;

        case (AllOpenPosted::Open):
           qbds.addRange(fieldnum(VendSpListJour,SpClosed)).value('1');
        break;

        case (AllOpenPosted::Posted):
           qbds.addRange(fieldnum(VendSpListJour,SpClosed)).value('0');
        break;
        default:
        break;
    }
    qbds = query.dataSourceTable(tablenum(VendInvoiceTrans));
    
    if (_count_query)
    {
        qbds.addSelectionField(fieldnum(VendinvoiceTrans,RecId), SelectionField::Count);
    }
    
    qbds = query.dataSourceTable(tablenum(VendSpListTrans));

    return (new QueryRun(query));
   
}

валится на qbds = query.dataSourceTable(tableNum(VendSpListJour));
qbds.addLink(fieldnum(VendSpListTrans,InternalSp),fieldnum(VendSpListJour,InternalSP));

Если заккоментировать весь кусок от qbds = query.dataSourceTable(tableNum(VendSpListJour));
и до конца switch - всё замечательно работает.

Уважаемый maldini, не знаете, в чём причина?

user_ax · 24.05.2013, 17:29

А, я понял, я не добавляю датасорс, я пытаюсь узнать его id

ice · 24.05.2013, 17:39

если я вас правильно понял, то вам не сделать это одним запросом. делайте while select по двум таблицам, и в цикле группировку по VendInvoiceTrans

user_ax · 24.05.2013, 17:42

Цитата:

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

если я вас правильно понял, то вам не сделать это одним запросом. делайте while select по двум таблицам, и в цикле группировку по VendInvoiceTrans

Почему-то мне кажется, что я тоже не сделаю через query это.

Похожие темы
Тема	Автор	Раздел	Ответов	Посл. сообщение
AX 2012 Наследование таблиц. Краткое описание механизма	sukhanchik	DAX: Программирование	32	21.09.2018 17:56
emeadaxsupport: Calling the Query Service	Blog bot	DAX Blogs	0	18.02.2012 01:13
AIF: OData Query Service	Blog bot	DAX Blogs	0	24.08.2011 09:11
jinx: Dynamics AX – Query-Ranges und Filtereinstellungen des Benutzers	Blog bot	DAX auf Deutsch	0	04.02.2010 00:05
Dynamics AX Geek: Using query()	Blog bot	DAX Blogs	0	04.05.2009 14:05