outer join и агрегаты

[koraman]

Подскажите, пожалуйста, возможно ли в X++ использовать outer join с агрегатами? А то синтаксис языка таков, что будто это возможно... и было бы, конечно, очень удобно... И если возможно, как этого добиться? Пытаюсь сделать что-то наподобие следующего:

while select custTable
    outer join AccountNum, sum(AmountMST) from custTrans
        group by AccountNum
        where custTrans.AccountNum == custTable.AccountNum
{
}

Он же мне выводит пустоту...

[belugin]

попробуйте

while select max(recID) from custTable outer join ...

[koraman]

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

[belugin]

а что на сервер уходит? вообще непонятно зачем так делать, потому, что x + NULL = NULL

[NNB]

Я использовал промежуточные таблицы

[koraman]

Цитата:

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

а что на сервер уходит? вообще непонятно зачем так делать, потому, что x + NULL = NULL

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

Цитата:

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

Я использовал промежуточные таблицы

Аналогично.

[_scorp_]

А что конкретно у Вас не получается? У меня вот этот job отрабатывает нормально

static void Job(Args _args)
{
    Custtable custTable;
    Custtrans custTrans;
    ;
    while select custTable
    outer join sum(AmountMST) from custTrans
        group by AccountNum
        where custTrans.AccountNum == custTable.AccountNum
    {
        print strfmt("%1 %2", custTrans.AccountNum, custtrans.AmountMST);
    }
    pause;
}

[lev]

Папробуйте вот так:

while select TableId from custTable
    outer join sum(AmountMST) from custTrans
        group by AccountNum
        where custTrans.AccountNum == custTable.AccountNum
{
}

Таким образом у вас выберутся суммы сгруппированные по коду клиента

[koraman]

Это какая-то мистика! Я, конечно, в аксапте новичок... Она всегда такая непредсказуемая?
Сегодня после поста _scorp_ у меня заработал скрипт, который я выкладывал в начале!
Цель же - немножко посложнее. Мне необходимо вывести для каждого клиента список договоров с указанием суммарного оборота по каждому.
Я создал тестовую формочку с одним единственным гридом, который цепляется к временной табличке, которая перед отображением заполняется своим методом fill().

Итак. Проблема.

Объявление переменных:

    CustTable custTable;
    ContactPerson contactPerson;
    CustGroup custGroup;
    RContractTable contractTable;
    RContractTypes contractTypes;
    CustTrans custTrans;
    ;

Шаг 1:

    while select custTable order by AccountNum
        outer join contactPerson where
            custTable.ContactPersonId == contactPerson.ContactPersonId
    {
        this.CustInfo = custTable.AccountNum + ", " + contactPerson.Name;
        this.insert();
    } // end while

Здесь всё нормально. Выбирается, джойнится, отображается.

Шаг 2:

    while select custTable order by AccountNum
        outer join contactPerson where
            custTable.ContactPersonId == contactPerson.ContactPersonId
        outer join custGroup where
            (custGroup.dataAreaId == contactPerson.dataAreaId || custGroup.dataAreaId != contactPerson.dataAreaId) &&
            custTable.CustGroup == custGroup.CustGroup
    {
        this.CustInfo = custTable.AccountNum + ", " + contactPerson.Name;
        this.CustGroupName = custGroup.Name;
        this.insert();
    } // end while

Добавили ещё один джоин, стали отображать ещё один столбец - и появилась первая проблема! Почему-то у нескольких записей перестало отображаться название группы клиентов CustGroup.Name! Всё вроде бы нормально... В CustTable группа указана, в CustGroup она существует, для многих строк она в гриде отображается, но для некоторых не отображается. Почему?

Шаг 3:

    while select custTable order by AccountNum
        outer join contactPerson where
            custTable.ContactPersonId == contactPerson.ContactPersonId
        outer join custGroup where
            (custGroup.dataAreaId == contactPerson.dataAreaId || custGroup.dataAreaId != contactPerson.dataAreaId) &&
            custTable.CustGroup == custGroup.CustGroup
        outer join contractTable where
            (contractTable.dataAreaId == custGroup.dataAreaId || contractTable.dataAreaId != custGroup.dataAreaId) &&
            contractTable.RContractPartnerCode == custTable.AccountNum &&
            contractTable.RContractPartnerType == RContractPartnerType::Cust
        outer join contractTypes where
            (contractTypes.dataAreaId == contractTable.dataAreaId || contractTypes.dataAreaId != contractTable.dataAreaId) &&
            contractTypes.ContractCode == contractTable.RContractCode
    {
        this.CustInfo = custTable.AccountNum + ", " + contactPerson.Name;
        this.CustGroupName = custGroup.Name;
        this.ContractRegNo = contractTable.RContractAccount;
        this.ContractNo = contractTable.RContractNumber;
        this.ContractGroupName = contractTypes.ContractCodeName;
        this.insert();
    } // end while

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

Шаг 4:

    while select custTable order by AccountNum
        outer join contactPerson where
            custTable.ContactPersonId == contactPerson.ContactPersonId
        outer join custGroup where
            (custGroup.dataAreaId == contactPerson.dataAreaId || custGroup.dataAreaId != contactPerson.dataAreaId) &&
            custTable.CustGroup == custGroup.CustGroup
        outer join contractTable where
            (contractTable.dataAreaId == custGroup.dataAreaId || contractTable.dataAreaId != custGroup.dataAreaId) &&
            contractTable.RContractPartnerCode == custTable.AccountNum &&
            contractTable.RContractPartnerType == RContractPartnerType::Cust
        outer join contractTypes where
            (contractTypes.dataAreaId == contractTable.dataAreaId || contractTypes.dataAreaId != contractTable.dataAreaId) &&
            contractTypes.ContractCode == contractTable.RContractCode
        outer join AccountNum, sum(AmountMST) from custTrans group by AccountNum where
            (custTrans.dataAreaId == contractTypes.dataAreaId || custTrans.dataAreaId != contractTypes.dataAreaId) &&
            custTrans.RContractAccount == contractTable.RContractAccount &&
            custTrans.RContractCode == contractTable.RContractCode
    {
        this.CustInfo = custTable.AccountNum + ", " + contactPerson.Name;
        this.CustGroupName = custGroup.Name;
        this.ContractRegNo = contractTable.RContractAccount;
        this.ContractNo = contractTable.RContractNumber;
        this.ContractGroupName = contractTypes.ContractCodeName;
        this.Amount = custTrans.AmountMST;
        this.insert();
    } // end while

Вот здесь уже всё валится. И по-разному. Вчера это был просто пустой грид, а сегодня уже сообщение типа "Невозможно выбрать запись в CustTable. База данных SQL обнаружила ашипку.".

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

[koraman]

Ой, база данных SQL обнаружила ошибку потому, что я забыл убрать order by в while select custTable. То есть итоговый запрос выглядит так:

    while select custTable
        outer join contactPerson where
            custTable.ContactPersonId == contactPerson.ContactPersonId
        outer join custGroup where
            (custGroup.dataAreaId == contactPerson.dataAreaId || custGroup.dataAreaId != contactPerson.dataAreaId) &&
            custTable.CustGroup == custGroup.CustGroup
        outer join contractTable where
            (contractTable.dataAreaId == custGroup.dataAreaId || contractTable.dataAreaId != custGroup.dataAreaId) &&
            contractTable.RContractPartnerCode == custTable.AccountNum &&
            contractTable.RContractPartnerType == RContractPartnerType::Cust
        outer join contractTypes where
            (contractTypes.dataAreaId == contractTable.dataAreaId || contractTypes.dataAreaId != contractTable.dataAreaId) &&
            contractTypes.ContractCode == contractTable.RContractCode
        outer join RContractAccount, RContractCode, sum(AmountMST) from custTrans group by RContractAccount, RContractCode where
            (custTrans.dataAreaId == contractTypes.dataAreaId || custTrans.dataAreaId != contractTypes.dataAreaId) &&
            custTrans.RContractAccount == contractTable.RContractAccount &&
            custTrans.RContractCode == contractTable.RContractCode
    {
        this.CustInfo = custTable.AccountNum + ", " + contactPerson.Name;
        this.CustGroupName = custGroup.Name;
        this.ContractRegNo = contractTable.RContractAccount;
        this.ContractNo = contractTable.RContractNumber;
        this.ContractGroupName = contractTypes.ContractCodeName;
        this.Amount = custTrans.AmountMST;
        this.insert();
    } // end while

Вот. Но тем не менее грид оказывается таким же пустым... и с группами всё так же...

Да, кстати. В таблице CustTrans нет записей, соответствующих записям из RContractTable, но ведь используется OUTER join.

[mazzy]

Цитата:

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

Это какая-то мистика!

Барабашки нет.

Цитата:

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

Шаг 2:

    while select custTable order by AccountNum
        outer join contactPerson where
            custTable.ContactPersonId == contactPerson.ContactPersonId
        outer join custGroup where
            (custGroup.dataAreaId == contactPerson.dataAreaId || custGroup.dataAreaId != contactPerson.dataAreaId) &&
            custTable.CustGroup == custGroup.CustGroup
    {
        this.CustInfo = custTable.AccountNum + ", " + contactPerson.Name;
        this.CustGroupName = custGroup.Name;
        this.insert();
    } // end while

Очень странное условие в запросе.

Цитата:

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

Добавили ещё один джоин, стали отображать ещё один столбец - и появилась первая проблема! Почему-то у нескольких записей перестало отображаться название группы клиентов CustGroup.Name!

Читайте про outer join на этом форуме.
Аксапта плохо переваривает несколько outer join'ов.

Почему? Потому что разыменование в Аксапте делается совершенно другим способом. Вы привыкли к LEFT JOIN'ам и считаете, что это быстро. Нет, в Аксапте очень много зявязано на кэширование записей (читайте про кэширование в транзакции, вне транзакции, а также кэширование таблиц).

Суть Аксапта-подхода:
1. Естественные коды (коды должны быть такими, что легко читаются и понимаются человеком). В результате потребность в разыменовании резко снижается. См. стандартные формы и отчеты. См. неоднократные обсуждения на этом форуме и здесь http://axapta.mazzy.ru/lib/autonumber/
2. Использовать простые запросы с вложенными простыми и КЭШИРУЕМЫМИ запросами.

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

    while select custTable order by AccountNum
        outer join contactPerson where
            custTable.ContactPersonId == contactPerson.ContactPersonId
    {
        custGroup == custGroup::find(custTable.CustGroup);
    }

Почему так? Потому что внешний запрос будет гораздо проще и поэтому будет быстрее возвращаться. Возвращаемый массив данных будет меньше (что чертовски важно для тонких каналов)

А вот внутренний запрос будет выполняться на SQL только в первый раз за пользовательскую сессию. как правило, результаты будет браться из кэша КЛИЕНТА.

Вывод - не усложняйте запросы. В Аксапте не всегда вложенные запросы плохо.
Детально разберитесь с кэшированием записей.

[lev]

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

P.S. mazzy прав.

[mazzy]

Цитата:

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

То есть итоговый запрос выглядит так:

Ужас.

[koraman]

Значит, не всё так просто... что ж... будем читать.
Спасибо за разъяснения!

[belugin]

http://erpkb.com/Axapta/MonitoringZaprosovSQL

[belugin]

можно попробовать сделать сгруппированное view и его уже аутер джоинить