Проблема с SQL запросом

[kalex_a]

Цитата:

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

Первое - уберите проверку на компанию - Axapta сама сообразит
Второе - используйте while select c группировкой и при поиощи if проверяйте ограничения.
При этом можно заполнять какую-либо таблицу

Я в аксапте новичек не подскажете как через while select правильно написать? А то у меня постоянно "Синтаксическая ошибка", хотя кажется что правильно написал.

[Alexius]

Попробуйте через класс Connection для соединения с текущей БД Аксапты:

static void Job_VendReport()
{
    Connection  con = new Connection();
    Statement       stmt = con.createStatement();
    ResultSet  resultSet;
    str sqlExpression ="select VTr.AccountNum,VT.Name,VTr.RContractAccount,RT.RContractCode from VendTrans VTr "+
    "join vendtable VT on VT.AccountNum=VTr.AccountNum and VT.DataAreaId='dat' "+
    "left join RContractTable RT on RT.RContractAccount=VTr.RContractAccount and RT.DataAreaId='dat' "+
    "where VTr.DataAreaId='dat' and VTr.closed=''"+
    "group by VTr.AccountNum, VT.Name, VTr.RContractAccount,RT.RContractCode  having count(*)>1 and sum(VTr.AmountCur)=0 "+
    "order by RT.RContractCode";
    ;
    resultSet = stmt.executeQuery(sqlExpression);
    while (resultSet.next())
    {
        info(resultSet.getString(1));
    }
}

[mazzy]

Цитата:

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

    UserConnection  con = new UserConnection();
...
        info(resultSet.getString(1));
...

Цитата:

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

Попробуйте через класс Connection для соединения с текущей БД Аксапты:

Зачем же вы человеку плохое советуете?

Цитата:

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

Здравствуйте, подскажите пожалуйста, как сделать такой запрос в Axapta? и что использовать лучьше View(это было бы лучьше), Query или какой нибудь while select, просто не знаю, знаю как простые запросы делать, а такие .

Во-первых, обязательно прочитайте про режимы кэширования таблиц в Аксапте. Из-за режимов кэширования в Аксапте зачастую писать вложенные И простые запросы выгоднее, нежели один навороченный супер-запрос с кучей join'ов.
Во-вторых, среди программистов Аксапты не принято сокращать суффиксы (типа Trans) и префиксы (типа Vend). Прочитайте хелп для разработчика на предмет соглашений по наименованию объектов.
В-третьих, обязательно старайтесь вникнуть в предметную область. Сумма по полю AmountCur не имеет никакого смысла, если вы не накладываете никаких условий на валюту. Сейчас ваш запрос суммирует рубли, тугрики, евро, доллары и т.д. и проверяет получившуюся сумму на ноль. Смысла в этом условии - никакого.
В-четвертых, обязательно поймите смысл полей. Так вы делаете выборку с условием (VTr.closed='') и добавляете having sum(VTr.AmountCur)=0. Вообще говоря, в Аксапте если проводка НЕ закрыта, то ее надо анализировать и выбирать даже если сумма оплат с накладными равна 0. Такова логика Аксапты. Система перестает анализировать только Закрытые записи. Ваш Having одним махом нарушает логику работы стандартного функционала и превращает Аксапту в 1С
В-пятых, поймите соотношение таблиц и данных в таблицах. Так в Аксапте для открытых проводок по клиентам и поставщикам есть специальная таблица VendTransOpen, которая содержит только открытую часть из проводок по поставщикам.
В-шестых, таблица RContractTable содержит договоры как с поставщиками, так и с клиентами. Поэтому делать выборку из RContractTable без указания типа нельзя!!! Ведь у вас могут быть клиенты и поставщики с одинаковым кодом.
В-седьмых, поскольку вы не разбирались с предметной областью и с таблицами, вы указали сортировку по полю, по которому нет индекса. В итоге ваша супер-оптимизация-в-один-запрос идет лесом из-за того, что сортировка будет происходить в temp-базе данных SQL-сервера.
В-восьмых, вы неправильно связали таблицу RContractTable и VendTrans - не все поля указали.
В-девятых, поле Closed имеет тип Date

Поэтому правильный ответ на ваш вопрос - ни в коем случае не нужно делать так как вы хотите. Если вам дал задачу некий "постановщик" или "аналитик" - четвертуйте его. Можно медленно.

Постараемся понять что же вы делаете и сформулировать на нормальном человеческом языке:

Вы хотите получить список различных поставщиков (код, наименование) с кодами их договоров по которым есть хоть какие-то незакрытые суммы (список должен быть отсортирован по коду договора)

При реверс-инжиниринге вашего запроса возникает неопределенность - каким образом вы трактуете незакрытость. Стандартная Аксапта использует только признак закрытости - этот признак устанавливается ПОСЛЕ операции сопоставления (никаких проверок сумм на ноль). Вы зачем-то вводите условие, что сумма должна быть ненулевой, что тут же отменяет функционал сопоставления (и тут же вносит кучу багов с курсовыми разницами и разными валютами). Поэтому, скорее всего, условие в Having - это неправильное условие (Я надеюсь, что вы не хотели сломать Аксапту сознательно).

Поэтому запрос, аналогичный вашему, в Аксапте должен быть таким:

static void Job11(Args _args)
{
    RContractTable  RContractTable;
    VendTrans       VendTrans;
    VendTransOpen   VendTransOpen;
    VendTable       VendTable;


    while select RContractTable
        index ContractTypeCodeAccountIdx
        where RContractTable.RContractPartnerType == RContractPartnerType::Vend
    {
        select firstonly VendTrans
            where VendTrans.RContractAccount == RContractTable.RContractAccount
        exists join VendTransOpen
            where VendTrans.AccountNum == VendTransOpen.AccountNum
               && VendTrans.RecId == VendTransOpen.RefRecId;

        if( !VendTrans )
            continue;

        VendTable = VendTable::find(RContractTable.RContractAccount);
        info(strfmt("%1 %2 %3 %4", VendTable.AccountNum, VendTable.Name,RContractTable.RContractAccount,RContractTable.RContractCode));
    }

}

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

Пооптимизируем:
Если честно, то я не вижу абсолютно никакого смысла сортировать результаты выдачи по КОДУ договора. Если убрать требование сортировки по коду и в цикле договоры не нужны, то запрос может быть гораздо простым и вполне Аксаптовским:

static void Job12(Args _args)
{
    //RContractTable  RContractTable; // Раскоментируйте, если внутри цикла нужны данные из договора
    VendTrans       VendTrans;
    VendTransOpen   VendTransOpen;
    VendTable       VendTable;


    while select count(recid) from VendTrans
        group by AccountNum, RContractAccount, RContractCode
    exists join VendTransOpen
        where VendTrans.AccountNum == VendTransOpen.AccountNum
           && VendTrans.RecId == VendTransOpen.RefRecId
    {
        //RContractTable = RContractTable::find(RContractPartnerType::Vend, VendTrans.RContractCode, VendTrans.RContractAccount);
        VendTable = VendTable::find(VendTrans.AccountNum);
        info(strfmt("%1 %2 %3 %4", VendTable.AccountNum, VendTable.Name,VendTrans.RContractAccount,VendTrans.RContractCode));
    }

}

Но вообще говоря, в проводках Аксапты есть и суммы, и признак закрытости. Поэтому, если подумать и посмотреть в метод open таблицы VendTrans (а также если внутри цикла не нужны данные из VendTransOpen), то запрос становится вообще тривиальным:

static void Job14(Args _args)
{
    VendTrans       VendTrans;
    VendTable       VendTable;

    while select sum(AmountMST), sum(SettleAmountMST) from VendTrans
        group by AccountNum, RContractAccount, RContractCode
        where VendTrans.amountCur != VendTrans.settleAmountCur // условие взято из метода Open() таблицы VendTrans
    {
        VendTable = VendTable::find(VendTrans.AccountNum);
        info(strfmt("%1 %2 %3 %4", VendTable.AccountNum, VendTable.Name,VendTrans.RContractAccount,VendTrans.RContractCode));
    }

}

И тут можно подумать над тем, сравнивать ли поле Closed c datenull() или все-таки пользоваться условием на неравенство сумм из метода open()... Что будет оптимальнее, надо смотреть в базу. Более правильным, с точки зрения Аксапты, является условие из метода open().


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

[mazzy]

Цитата:

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

Поэтому запрос, полностью аналогичный вашему, в Аксапте должен быть таким:

Хорошенько подумав, можно и этот запрос (с сортировкой по коду договора) сильно соптимизировать:

static void Job11(Args _args)
{
    VendTrans       VendTrans;
    VendTable       VendTable;

    while select sum(AmountMST), sum(SettleAmountMST) from VendTrans
        group by RContractCode, RContractAccount, AccountNum
        where VendTrans.amountCur != VendTrans.settleAmountCur
    {
        VendTable = VendTable::find(VendTrans.AccountNum);
        info(strfmt("%1 %2 %3 %4", VendTable.AccountNum, VendTable.Name,VendTrans.RContractAccount,VendTrans.RContractCode));
    }

}

Другое дело, что в VendTrans нет индекса по RContractCode...
Но это уже совсем другая история, связанная с полным отсутствием оптимизации локального функционала...

===========
и если этот запрос делается внутри транзакции, то при отсутствии индекса по RContractCode может быть стоит VendTable внести в сам запрос, а не использовать метод find... Но это уже нужно смотреть в конкретный код и в план запроса...