Invalid source query specified for insert_Recordset.

(1) Share

Report

Posted on by AbdullahAhmed_

190

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!

All responses (12)

Answers (1)

Verified answer

AbdullahAhmed_ 190 on at

Like (1)

Report
Invalid source query specified for insert_Recordset.

Mr. Martin is right..

I actually found the solution.. it's simple.

If you wanna join a datasource you have to add a selection field for it and map that field. If the field isn't selected or not mapped it won't work. So, the solution would look like this:

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.addSelectionField(fieldNum(XDSCustTable,UserId)); 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)]); fieldMapping.insert(fieldStr(XDSSalesTable,UserId) ,[qbds.uniqueId(),fieldStr(XDSCustTable,UserId)]); Query::insert_recordset(xDSSalesTable,fieldMapping,q);

but what if you want to join a datasource but you don't want to get the value in the target table? like, in my example for my target table (XDSSalesTable) it has 2 fields (SalesId and UserId), I get SalesId from SalesTable and UserId from the joined datasource... But what if I want to get the SalesId only and keep UserId empty??

the solution is to specify the join mode between the joined datasource and the parent datasource like that:

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

ExistJoin will not retrieve any value from the joined datasource!
Martin Dráb 230,370 Most Valuable Professional on at

Like (1)

Report
Invalid source query specified for insert_Recordset.

It's not true that the query used for Query::insert_recordset() can have a single data source only.

There are plenty of examples of multiple data sources in the standard application, e.g. in AccountingSourceExplorerProcessor.filterByDimensionFocus():

private void filterByDimensionFocus(DimensionAttributeValueCombination _dimensionFocus) { ... Query dimensionQuery = new Query(queryStr(AccountingSourceExplorerQuery)); ... QueryBuildDataSource qbdsGeneralJournalAccountEntry = dimensionQuery.dataSourceTable(tableNum(GeneralJournalAccountEntry)); QueryBuildDataSource qbdsDimensionAttributeValueCombination = dimensionQuery.dataSourceTable(tableNum(DimensionAttributeValueCombination)); qbdsDimensionAttributeValueCombination.joinMode(JoinMode::InnerJoin); ... Query::insert_recordset(generalJournalAccountEntryTmp, fieldMapping, dimensionQuery); ... }

AccountingSourceExplorerQuery consists of three data sources.
Suggested answer

Saif Ali Sabri 370 on at

Like (1)

Report
Invalid source query specified for insert_Recordset.

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.
AbdullahAhmed_ 190 on at

Like (1)

Report

Invalid source query specified for insert_Recordset.

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??
Martin Dráb 230,370 Most Valuable Professional on at

Like (1)

Report

Invalid source query specified for insert_Recordset.

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).
AbdullahAhmed_ 190 on at

Like (1)

Report
Invalid source query specified for insert_Recordset.

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!
Martin Dráb 230,370 Most Valuable Professional on at

Like (1)

Report
Invalid source query specified for insert_Recordset.

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.
AbdullahAhmed_ 190 on at

Like (1)

Report

Invalid source query specified for insert_Recordset.

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!
Martin Dráb 230,370 Most Valuable Professional on at

Like (1)

Report

Invalid source query specified for insert_Recordset.

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().
AbdullahAhmed_ 190 on at

Like (1)

Report
Invalid source query specified for insert_Recordset.

- 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)