Invalid source query specified for insert_Recordset.

(1) Share

Report

Posted on by AbdullahAhmed_

440

I use this query and I get this error: 'Invalid source query specified for insert_Recordset.'

        XDSSalesTable               xDSSalesTable;
        Query                       q;
        QueryBuildDataSource        qbds;
        QueryBuildDataSource        qbds_Joined;
        QueryBuildFieldList         qbfl;
        Map                         fieldMapping;

        q               = new Query();
        qbds            = q.addDataSource(tableNum(SalesTable));
        qbds.addSelectionField(fieldNum(SalesTable,SalesId));
        qbds.addRange(fieldNum(SalesTable,SalesStatus)).value('3');
        qbfl            = qbds.fields();
        qbfl.dynamic(QueryFieldListDynamic::No);
       

        qbds_Joined = q.dataSourceTable(tableNum(SalesTable)).addDataSource(tableNum(XDSCustTable));
        qbds_Joined.addLink(fieldNum(SalesTable,InvoiceAccount),fieldNum(XDSCustTable,CustAccount));
        qbds_Joined.addRange(fieldNum(XDSCustTable,UserId)).value('TestUser');

        fieldMapping    = new Map(Types::String,Types::Container);

        ttsBegin;
        fieldMapping.insert(fieldStr(XDSSalesTable,SalesId)      ,[qbds.uniqueId(),fieldStr(SalesTable,SalesId)]);
        Query::insert_recordset(xDSSalesTable,fieldMapping,q);
                    
        update_recordset xDSSalesTable
            setting UserId = 'TestUser'
            where xDSSalesTable.UserId == '';
        ttscommit;

and this is the value of the query variable q :

{Query  object c3143e70: 
SELECT SalesId FROM SalesTable(SalesTable_1) 
    WHERE (((SalesStatus == "3")) 
JOIN CustAccount FROM XDSCustTable(XDSCustTable_1) 
   ON SalesTable.InvoiceAccount = XDSCustTable.CustAccount 
   AND ((UserId = N'TestUser'))}

which returns result when written in sql server!

I have the same question (1)

All responses (12)

Answers (1)

Martin Dráb 237,833 Most Valuable Professional on at

Like (0)

Report

It's a pity that you ignore the code improvements I gave you in your previous thread and you keep using your all code with all the useless statement (e.g. clearing ranges on a newly added data sources), cryptic variable names and so on. You make your code difficult to understand for everyone, including (and most importantly) yourself. But I'm not going to fix it again if you aren't interested.

The first thing I would fix is removing the duplicate call of addLink(). Maybe it's not the cause of your current problem, but it's wrong for sure, therefore you should address it in either case.

Another problem I see is that you're comparing SalesStatus with string values, but SalesStatus is an enum (= integer in database).

Was this reply helpful? Yes No
Martin Dráb 237,833 Most Valuable Professional on at

Like (0)

Report

Isn't it wrong that your query select fields from XDSCustTableRegular? Should you use an exists join?

Was this reply helpful? Yes No
AbdullahAhmed_ 440 on at

Like (1)

Report
- The classes and tables are required to have DDV as a prefix so, that makes the names weird.

- If I removed addLink(), how would the 2 datasources join... I mean what would be the join condition if I didn't specify it?

- SalesStatus is stored as numbers in the database and I checked the query and it turns the required result.

- What I'm trying to acheive is: I have a security policy that has CustTable as a primary table and SalesTable as constrained.

In my setup form there's a condition that filters CustTable and another condition that filters SalesTable and I have like a Tmp table (XDSSalesTable) that I'm trying to insert the records into...

Instead of inserting SalesId into (XDSSalesTable) depending on the condition that filters SalesTable:

((SalesStatus == "1") ||(SalesStatus == "3"))

which returns most of the SalesId in the system (+700k records)... when I join SalesTable with another Tmp that has CustAccount of the desired customers (XDSCustTable) Then I only get (60 records)

Was this reply helpful? Yes No
Martin Dráb 237,833 Most Valuable Professional on at

Like (1)

Report

I didn't say that you should remove addLink() completely. I'm saying that calling addLink() twice with exactly the same parameters is useless. You should throw away one of these calls.

And by the way, you should define the relation on a table and then merely call relations(true), rather than resorting to addLink().

Was this reply helpful? Yes No
AbdullahAhmed_ 440 on at

Like (1)

Report

Sorry I didn't notice the duplication.. I updated the code to be more readable.

I still don't know why I'm facing this error... when I use a single datasource the Query::insert_recordset works fine, but when I join another datasource it's throwing the error!

Was this reply helpful? Yes No
Martin Dráb 237,833 Most Valuable Professional on at

Like (1)

Report
Let me repeat: isn't it wrong that you return fields from XDSCustTable? Have you tried my suggestion to avoid that by using an exists join instead of the inner join?

For example:
Query q = new Query(); QueryBuildDataSource salesTableDs = q.addDataSource(tableNum(SalesTable)); qbds.fields().dynamic(QueryFieldListDynamic::No); qbds.addSelectionField(fieldNum(SalesTable, SalesId)); qbds.addRange(fieldNum(SalesTable, SalesStatus)).value(queryValue(SalesStatus::Delivered)); QueryBuildDataSource xdsCustTableDs = salesTableDs.addDataSource(tableNum(XDSCustTable)); xdsCustTableDs.joinMode(JoinMode::ExistJoin); xdsCustTableDs.relations(true); xdsCustTableDs.addRange(fieldNum(XDSCustTable, UserId)).value(queryValue('TestUser'));
Please notice also the correct way of setting an enum range value.

Was this reply helpful? Yes No
AbdullahAhmed_ 440 on at

Like (1)

Report
Yes I don't want fields from XDSCustTable.. I just want to apply a range on SalesId coming from SalesTable.

The addRelation(true) doesn't specify the relation between SalesTable and XDSCustTable

and the query is like this:

SELECT SalesId FROM SalesTable(SalesTable_1) WHERE (((SalesStatus == "3")) EXISTS JOIN *FROM XDSCustTable(XDSCustTable_1) where((UserId = N'TestUser'))}

and still the same error!

Was this reply helpful? Yes No
Martin Dráb 237,833 Most Valuable Professional on at

Like (1)

Report

I said that you should define the relation on a table and then merely call relations(true). If relations(true) doesn't add a join condition, it likely means that you forgot to add the relation (on XDSCustTable).

Was this reply helpful? Yes No
AbdullahAhmed_ 440 on at

Like (1)

Report

Thank you, that makes sense.. I don't prefer to use relations(true) or the use relations property on the datasource level because there might be more than 1 relation between 2 tables and the system might not pick the right one that fit my condition or in this case when I didn't specify a relation on XDSCustTable table level and the query didn't get affected...

That's why I prefer to use addLink() or specify the relation fields on the table level instead of setting use relation property to true.

Anyway, I want to understand the error.. why it's happening?

'Invalid source query specified for insert_Recordset.' why the source is invalid? It works well if it's only 1 datasource and when I join another datasource it's now invalid??

Was this reply helpful? Yes No
Suggested answer

Saif Ali Sabri 2,351 Super User 2025 Season 2 on at

Like (1)

Report
The error 'Invalid source query specified for insert_Recordset' occurs because insert_recordset does not support complex queries with joined tables or linked data sources. The insert_recordset command expects a query with a single data source, so adding a joined data source (qbds_Joined) in this case is invalid.

Use a simple select statement instead of insert_recordset for inserting records with joined conditions.

Create a temporary table to store the query results before inserting them into the XDSSalesTable.

Was this reply helpful? Yes No

Join the conversation

Community site session details

Invalid source query specified for insert_Recordset.

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Invalid source query specified for insert_Recordset.

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates