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

[kalex_a]

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

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

[Alexius]

По "хорошему" никак

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

Насколько я знаю, опция HAVING в Axapta не поддерживается. Ни в каком виде. Единственное "штатное" решение - это сканировать всю выборку и вручную исключать записи удовлетворяющие условию HAVING.

Хотя, условие count(*)>1 еще можно попробовать решить через NOT EXISTS, но условие sum(VTr.AmountCur)=0 в общем случае без HAVING не решается...

[kalex_a]

Я еще вот накопал типа прямого обращения базе, но выдает такую ошибку.
Ошибка:
Сбой запроса на разрешение типа "SqlStatementExecutePermission".
(S)\Classes\SqlStatementExecutePermission\demand
(S)\Classes\Statement\executeQuery

вот код, мож кто знает в чем проблема

static void Job_VendReport()
{
    UserConnection  con = new UserConnection();
    Statement       stmt = con.createStatement();
    ResultSet  resultSet;
    SqlStatementExecutePermission ssep;
    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";
    ;
    ssep =  new SqlStatementExecutePermission(sqlExpression);
    ssep.assert();
    resultSet = stmt.executeQuery(sqlExpression);
    while (resultSet.next())
    {
        info(resultSet.getString(1));
    }
}

[E S V]

stmt.executeQuery(sqlExpression) должно выполняться на сервере

[NNB]

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

[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... Но это уже нужно смотреть в конкретный код и в план запроса...

[_scorp_]

static void Job_VendReport()
{
    UserConnection  con = new UserConnection();
    Statement       stmt = con.createStatement();
    ResultSet  resultSet;
    SqlStatementExecutePermission ssep;
    InteropPermission                   comPermission;
    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";
    ;
    comPermission = new InteropPermission(InteropKind::ComInterop);
    comPermission.assert();
    ssep =  new SqlStatementExecutePermission(sqlExpression);
    ssep.assert();
    resultSet = stmt.executeQuery(sqlExpression);
    while (resultSet.next())
    {
        info(resultSet.getString(1));
    }
    CodeAccessPermission::revertAssert();
}

[NNB]

Что-нибудь в таком духе. Писал впопыхах. Не перечитывал. Мог ошибиться.

while select AccountNum, RContractAccount, Sum(AmountCur) from VTr group by RContractAccount
join Name from VT group by AccountNum, Name where VT.AccountNum==VTr.AccountNum
join RContractCode from RT group by RContractCode where RT.RContractAccount==VTr.RContractAccount &&
VTr.closed {
// if .....
//Что-нибудь insert()
}

[Wamr]

Сергей, красиво и поучительно...
но
Во-первых, за полтора года ответ, наверное, уже нашли
Во-вторых, нужны были поставщики\договора, по которым есть открытые проводки, но сумма по которым = 0

[mazzy]

Да... Дату заметил только у последнего сообщения... Извините.

Цитата:

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

Во-первых, за полтора года ответ, наверное, уже нашли

Да, я собственно хотел сказать участникам: "перестаньте советовать прямые запросы. executeQuery - на крайний случай". Но стер...

Цитата:

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

Во-вторых, нужны были поставщики\договора, по которым есть открытые проводки, но сумма по которым = 0

Открытая проводка не может быть с нулевой суммой
Единственный вариант - курсовая разница, когда (AmountCur = 0) && (AmountMST != 0)

В этом как раз и был изначальный вопрос - что считать "открытой/закрытой" проводкой
С точки зрения Аксапты - открытая проводка, то та у которой (VendTrans.amountCur != VendTrans.settleAmountCur).

Поэтому формулировка "есть открытые проводки, но сумма по которым = 0" - не совсем точна.

[Wamr]

"сумма по которым = 0" это having(sum(AmountCur)) = 0, а не where AmountCur = 0

то есть поставщик\договор, по которому примерно такая ситуация:
накладная -500руб
оплата 300руб
оплата 200руб

[mazzy]

Цитата:

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

"сумма по которым = 0" это having(sum(AmountCur)) = 0, а не where AmountCur = 0

то есть поставщик\договор, по которому примерно такая ситуация:
накладная -500руб
оплата 300руб
оплата 200руб

Если эти движения не сопоставлены, то, с точки зрения Аксапты, накладная не оплачена.
Аксапта будет предлагать/создавать новую оплату для этой несопоставленной накладной.
правильное условие оплаченности сформулировано в методе open таблицы VendTrans.

Повторюсь:

Цитата:

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

В-третьих, обязательно старайтесь вникнуть в предметную область. Сумма по полю AmountCur не имеет никакого смысла, если вы не накладываете никаких условий на валюту. Сейчас ваш запрос суммирует рубли, тугрики, евро, доллары и т.д. и проверяет получившуюся сумму на ноль. Смысла в этом условии - никакого.

В-четвертых, обязательно поймите смысл полей. Так вы делаете выборку с условием (VTr.closed='') и добавляете having sum(VTr.AmountCur)=0. Вообще говоря, в Аксапте если проводка НЕ закрыта, то ее надо анализировать и выбирать даже если сумма оплат с накладными равна 0. Такова логика Аксапты. Система перестает анализировать только Закрытые записи.

И еще:

Цитата:

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

Ваш Having одним махом нарушает логику работы стандартного функционала и превращает Аксапту в 1С