Skip to main content

Notifications

Dynamics 365 Community / Forums / Finance forum / Error when updating re...
Finance forum
Suggested answer

Error when updating records

Posted on by 154
Hi,

I used sysDa framwork, however i got this error:
The operation that you are performing generates a SQL statement that contains too many nested statements. Break the operation into multiple parts, and try again.
 
Here's what I did:
 
public class AService extends SysOperationServiceBase
{
    Table1              table1, table1Update;
    Query               query;
    QueryRun         queryRun;
 
 
    public void process(Table1Contract _contract)
    {
        AEnum enum1, enum2;
        query    = _contract.getQuery();
        queryRun = new QueryRun(query);
 
        enum2  = str2Enum(enum1, _contract.parmAEnum());
 
        SysDaQueryExpression queryExpr;
        SysDaFieldExpression fieldExpr = new SysDaFieldExpression(table1Update, fieldStr(Table1, RecId));
 
        while(queryRun.next())
        {
            table1   = queryRun.get(tableNum(Table1));
            
            if(queryExpr)
            {
                queryExpr = queryExpr.or(new SysDaEqualsExpression(fieldExpr, new SysDaValueExpression(table1.RecId)));
            }
            else
            {
                queryExpr = new SysDaEqualsExpression(fieldExpr, new SysDaValueExpression(table1.RecId));
            }
            
        }
 
        
         ttsbegin;
 
        SysDaUpdateObject updateObj         = new SysDaUpdateObject(table1Update);
        SysDaSettingsList sysDaSettingsList = new SysDaSettingsList();
        sysDaSettingsList.add(fieldStr(Table1, AEnum), new SysDaValueExpression(enum2));
 
        updateObj.settingClause(sysDaSettingsList);
        updateObj.whereClause(queueExpr);
 
        new SysDaUpdateStatement().update(updateObj);
 
        ttscommit;
 
    }

Please note that the error appears when the queryExp contains alot of ranges like more than 5K records

 
I replaced it with the following and it worked:
 
 
public class AService extends SysOperationServiceBase
{
 
 
 
    public void process(Table1Contract _contract)
    {
        
        Aenum enum1, enum2;
        System.Exception                   ex;
        Map                                updateFieldMapping   = new Map(Types::String, Types::String);
        Query                              query                = _contract.getQuery();
 
        enum2  = str2Enum(enum1, _contract.parmAEnum());
 
        updateFieldMapping.insert(fieldStr(Table1, AEnum), any2Str(enum2));
 
        try
        {
            ttsbegin;
            Query::update_recordset(updateFieldMapping, query);
            ttscommit;
        }
        catch(ex)
        {
            throw error(ex.Message);
        }
    }

1. How to fix the error I got using SysDa? what did I do wrong? 
2. What is better SysDa or Query::update_recordset and why?
3. when to use SysDa?
4. When to use Query::update_recordset?
 
 
 
 
  • Martin Dráb Profile Picture
    Martin Dráb 223,760 Super User on at
    Error when updating records
    It's all right - I think that most AX developers haven't ever used it. If you don't see any need for such an optimization (discussed before), then you don't need SysDa. It's not anything to worry about.
     
    But if you want real examples in the application anyway, use Find references.
  • CU04051814-0 Profile Picture
    CU04051814-0 154 on at
    Error when updating records
    Hi Martin,

    ok in general then, can u give me a real example on when to use SysDa? so that i understand it's benefit
  • Martin Dráb Profile Picture
    Martin Dráb 223,760 Super User on at
    Error when updating records
    I've never said that SysDa framework has no use (I mentioned its purpose in my first reply); here I was talking about that one single specific scenario you asked about (updating records by SysDa in cases when you can use Query::update_recordset()). Again, SysDa framework can do much more Query::update_recordset(). It's mainly used for querying data, but it can also delete records, create new ones, update records based on something else than a Query object and so on.
     
    Regarding update_recordset statement, you have conditions written in code. For example: where myTable.MyField == true. Ask yourself whether you can change the code when running the application. The answer is no - to change code, you need to do it in a development environment, build it and deploy the binaries.
  • CU04051814-0 Profile Picture
    CU04051814-0 154 on at
    Error when updating records
    Hi Martin,

    I'm assuming, if they did the sysDa framework, then definitely there should be a scenario where we should use it. But if u can't think of one, then it's fine. You've been a massive help already :)

    And btw you said it's also possible to use Query::update_recordset in case of design time. But would the opposite be possible? which is to use update_recordset in case of run time? I think no, right?
  • Martin Dráb Profile Picture
    Martin Dráb 223,760 Super User on at
    Error when updating records
    2. Honestly, I can't wouldn't consider SysDa at all when I can use update_recordset. I guess when you're writing a kernel feature where performance is super-critical, you may get some benefits from SysDa, but I've never run into such a situation. There are usually bigger performance problems elsewhere. Your original code is an example of such a preformance problem.
    3a. Yes, users interact with the running application, therefore it's at runtime. Users won't select records when you're writing code (design time).
    3b. When conditions are known at design time. Period.
  • CU04051814-0 Profile Picture
    CU04051814-0 154 on at
    Error when updating records
    Hi Martin,

    2. i mean can you give me an example of a a scenario where i should use sysDa instead of query::update_recordset  -- just to understand when sysDa should be an option as I can't understand it's usage
     
    3a. yes, the user selects records, then i set the query in controller class -- which means it's run-time
     
    3b. Ok so you mean if i'm updating all records not based on a selection or if query is known, then i should use update_recordset
  • Martin Dráb Profile Picture
    Martin Dráb 223,760 Super User on at
    Error when updating records
    1. You can use SysDa framework to update records in bulk - use SysDaUpdateStatement. But it doesn't sound meaninfgul in your case. You already have a query you can pass Query::update_recordset(), therefore that's what you should do. Analyzing the query and generating corresponding  SysDaUpdateObject sounds like a waste of time. And running the query, loading all records, adding a range for each record and running another query, as you did in your original code, would be even much worse.
    2. I'm sorry, but it's not clear to me what the example should demonstrate. Please explain what kind of information you're looking for.
    3a. I can't tell you what your scenario is; it's something you need to tell us. But you mentioned "the selection of data", so you should ask yourself when the selection is made. If it's done by users, it's clearly at runtime and now when you're writing your code.
    3. I didn't mean that using Query::update_recordset() is impossible if you know the query when writing code. My point is that it's not needed; you can do it simpler with update_recordset statement. A trivial example of knowing the query at design time: you want to change the state of all record from Processing to Processed.
  • CU04051814-0 Profile Picture
    CU04051814-0 154 on at
    Error when updating records
    Hi Martin,

    So what I'm doing is that there is a grid with data. And based on the selection of my data, i call the service to update the selected records.
    The query is set in controller class to add the selected records as a range

    Based on your replies:

    1. How I can do my requirement with SysDa? or is SysDa not suitable for this case?
     
    2. You said I should use SysDa when I need to generate dynamic query with better performance than when using Query* classes.  can you please give me an example?

    3. You said i should use Query::update_recordset if I know the query at runTime. And i should use update_recordset if i know the query at design time.
     3a. In my example I think I know the query at runTime, because i know the selected records before i call the service. right?
     3b. What is an example where i know the query at design time? and why Query::update_recordset wouldn't work for design time?
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 223,760 Super User on at
    Error when updating records
    1. The idea of adding more then 5000 conditions doesn't sound wise to me, therefore I consider it a problem of your design and not of the framwork.
    2. The main difference is that you add thousands of ranges using SysDa and none at all for update_recordset(), therefore you're comparing two completely different queries.
    3. Mainly when you need to generate dynamic query with better performance than when using Query* classes.
    4. When you want to update multiple records by a query generated at runtime. If you know the query at design time, use update_recordset statement.

Helpful resources

Quick Links

Contextual content recommendations & SharePoint Q&A

Instantly access relevant sales materials…

Beyond the DRA Cloud Label Printing

Explore cloud-based label printing within Dynamics 365…

HR Infrastructure Customer merge

Important considerations for HR Customer migrations…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,418 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 223,760 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,142

Featured topics

Product updates

Dynamics 365 release plans