Help with insert_RecordSet

(0) Share

Report

Posted on by Kaushik Parul

Hi Team,

I have a while statement which I want to convert in insert_RecordSet.

Table1      table1;
Table2      table2;
SalesTable  salesTable;

 while select table1
    join salesTable
        where table1.SalesId == salesTable.SalesId
            && salesTable.SalesStatus != SalesStatus::Backorder
    {
        table2.SalesId        = table1.SalesId;
        table2.CustAccount    = table1.CustAccount;
        table2.CreatedDate    = table1.CreatedDate;
        table2.SalesId        = table1.SalesId;
        table2.insert();
        
        delete_from table1
            where table1.SalesId    == table2.SalesId;
    }

I tried with below code but its not working..please help if its possible to convert this statement to insert_RecordSet?

Thanks in advance!!!!

 insert_recordset table2 
        (SalesId, CreatedDate, CustAccount)
    
    select SalesId, CreatedDate, CustAccount from table1
    join salesTable
        where table1.SalesId== salesTable.SalesId
            && !salesTable.SalesStatus   == SalesStatus::Backorder;

I have the same question (0)

All responses (6)

Answers (0)

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

Like (1)

Report

Could you tell us more about the problem, please? "Its not working" isn't very specific.

Was this reply helpful? Yes No
Kaushik Parul 47 on at

Like (0)

Report

HI Martin,

I get the error :

Cannot execute a data definition language command on Table1.

The SQL database has issued an error.

SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '='.

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

Like (1)

Report
I'm not sure if it's related, but please change

&& !salesTable.SalesStatus == SalesStatus::Backorder;

to

&& salesTable.SalesStatus != SalesStatus::Backorder;

Was this reply helpful? Yes No
Kaushik Parul 47 on at

Like (0)

Report

Hi Martin,

Thanks for the this..the error is gone now but the condition for open sales order is not working in this case.. Here what I am trying to do is...

Table1 has open sales order data..I am matching the status of the table1 records with salestable that whether the reocrd is still in open status in salestable...if its not open status the I am inserting the data in table2 and deleting it from table1.

(i.e., table1 should have only open sales orders data..if the status of the table1 sales orders has changed from open status to something else then I am deleting that record from table1 and inserting it to table2)

when running this insert record set condition..its just filling in all the data from table1 to table2 and deleting all records from table1 but not checking the "not open" condition.

Will this insertrecord set not work in this condition??

or is this condition incorrect?

please help..

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

Like (0)

Report
Let's focus on a single problem at once. You're talking about things like deleting data from Table1, but that's not what the insert_recordset statement does, therefore let's ignore it for now.

Also, you seem to be saying that you have a problem already with the query, therefore we maybe can forget insert_recordset as well.

You're saying that this query:

select SalesId, CreatedDate, CustAccount from table1 join salesTable where salesTable.SalesId == table1.SalesId && salesTable.SalesStatus != SalesStatus::Backorder;

is returning all the data from table1 and not checking the "not open" condition. Is that a correct description of your problem?

I must admit that I don't see anything wrong with the query. Just note that you could use exists/not exists joins instead of the inner join.

Was this reply helpful? Yes No
Kaushik Parul 47 on at

Like (0)

Report

You're saying that this query:

is returning all the data from table1 and not checking the "not open" condition. Is that a correct description of your problem?

Yes that's right...

Was this reply helpful? Yes No

Join the conversation

Community site session details

Help with 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

Help with 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