Query::Insert_RecordSet - The column name '<ColumnName>' is specified more than once in the SET clause or column list of an INSERT

[DotNet]

Hi,

I have a scenario that I need to build a query dynamically based on the Query form parameter filters and insert the data into an in memory temp table. I think I have built they query as I need and I can see how the query looks during debugging. But however, when I hit the line of the code

Query::insert_recordset(tempTable, targetToSourceMapping, query);

I get this error

I have looked at the and I don’t have the InventStyleId used twice in the insert clause. Just for the sake, I commented the lines of code InventSiteId, then it is complaining about the same thing for the InventSizeId column,

It seems like I am missing something and not aware of what is causing this error.

Have anybody come across this error before and was there a fix?

Thanks,

Dev

[MartinDrab]

We can’t find the problem in your code, because you didn’t show it.

Does it work if you remove all fields but one? If so, you’ll now that the problem is related to the fields you removed and you can add them one by one until you find the culprit. If it doesn’t work, you’ll have simple demonstration of the problem that you can share with us.

[DotNet]

Martin, I tried commenting the lines but the funny part is as of now with the current code it likes only one of the columns doesn’t matter which one. I have tried commenting one after the other and it only takes one column. I want to use the Query::Insert_Recordset so I can build ranges as I needed dynamically based on what user has selected. I don’t have the flexibility in the regular insert_RecordSet set based operation. Here is the code

public static MASProdActionFutureMarkedTmp  insertMASProdActionFutureMarkedTmp(TmpSysQuery _tmpSysQuery)
{
    MASProdActionFutureMarkedTmp        masProdActionFutureMarkedTmp;
    Query                               query = new Query();
    QueryBuildDataSource                qbdsProdTable;
    QueryBuildDataSource                qbdsInventDim;
    Map                                 targetToSourceMapping;
    str                                 rowCount;
    QueryBuildFieldList                 prodFieldList, inventDimFieldList;

    //delete_from masProdActionFutureMarkedTmp;
    query.clearAllFields();
    qbdsProdTable =  query.addDataSource(tableNum(Prodtable));
    prodFieldList = qbdsProdTable.fields();
    prodFieldList.dynamic(1);
    //qbdsProdTable.addSelectionField(fieldNum(ProdTable, ProdId));
    qbdsProdTable.addSelectionField(fieldNum(ProdTable, ItemId));
    //qbdsProdTable.addSelectionField(fieldNum(ProdTable, InventDimId));

    while select _tmpSysQuery
    {
        if(_tmpSysQuery.Tablelabel == 'ProdTable' && _tmpSysQuery.RangeValue)
        {
            qbdsProdTable.addRange(_tmpSysQuery.Field_Id).value(_tmpSysQuery.RangeValue);
        }
    }

    //qbdsInventDim = qbdsProdTable.addDataSource(tableNum(InventDim));
    //qbdsInventDim.relations(true);
    ////qbdsInventDim.addLink(fieldNum(ProdTable, InventDimId), fieldNum(InventDim, InventDimId));
    //inventDimFieldList = qbdsInventDim.fields();
    //inventDimFieldList.dynamic(1);
    //qbdsInventDim.addSelectionField(fieldNum(InventDim, ConfigId));
    //qbdsInventDim.addSelectionField(fieldNum(InventDim, InventcolorId));
    //qbdsInventDim.addSelectionField(fieldNum(InventDim, InventsizeId));
    //qbdsInventDim.addSelectionField(fieldNum(InventDim, InventStyleId));
//
//
//
    //while select _tmpsysquery
    //{
        //if(_tmpsysquery.tablelabel == 'inventdim' && _tmpsysquery.rangevalue)
        //{
            //qbdsinventdim.addrange(_tmpsysquery.field_id).value(_tmpsysquery.rangevalue);
        //}
    //}

    targetToSourceMapping = new Map(Types::String, Types::Container);
    //targetToSourceMapping.insert(fieldStr(masProdActionFutureMarkedTmp, ProdId), [qbdsProdTable.uniqueId(), fieldStr(ProdTable, ProdId)]);
    targetToSourceMapping.insert(fieldStr(masProdActionFutureMarkedTmp, ItemId), [qbdsProdTable.uniqueId(), fieldStr(ProdTable, ItemId)]);
    //targetToSourceMapping.insert(fieldStr(masProdActionFutureMarkedTmp, InventDimId), [qbdsProdTable.uniqueId(), fieldStr(ProdTable, InventDimId)]);
    //targetToSourceMapping.insert(fieldStr(masProdActionFutureMarkedTmp, ConfigId), [qbdsInventDim.uniqueId(), fieldStr(InventDim, ConfigId)]);
    //targetToSourceMapping.insert(fieldStr(masProdActionFutureMarkedTmp, InventColorId), [qbdsInventDim.uniqueId(), fieldStr(InventDim, InventColorId)]);
    //targetToSourceMapping.insert(fieldStr(masProdActionFutureMarkedTmp, InventSizeId), [qbdsInventDim.uniqueId(), fieldStr(InventDim, InventSizeId)]);
    //targetToSourceMapping.insert(fieldStr(masProdActionFutureMarkedTmp, InventStyleId), [qbdsInventDim.uniqueId(), fieldStr(InventDim, InventStyleId)]);
    ttsBegin;
    Query::insert_recordset(masProdActionFutureMarkedTmp, targetToSourceMapping, query);
    ttsCommit;
    rowCount = strFmt('%1', masProdActionFutureMarkedTmp.rowcount());
    info(strFmt('row count : %1', masProdActionFutureMarkedTmp.rowcount()));
    return masProdActionFutureMarkedTmp;
}

Where TmpSysQuery has all the parameters that users had selected. I want to be able to loop through it and add them to the query and pass it to the Query::Insert_Recordset.

here is what users select for instance

[MartinDrab]

Your code for defining selection fields looks weird to me. You call clearAllFields() before adding any data source, therefore it can’t do anything. Then you set the field list as dynamic (i.e. you include all fields) and then you add some of them again. Or least that’s my first impression.

[DotNet]

I commented the clearfields and dynamic code, Martin. So that can be ignored. I know that the clearfields line of code have no bearing with the error but was just put in as I was trying things out and see what might be causing the issue. I removed the dynamic property of the fields and only adding the ones that I want to addselctinField list. I put a sample job together with the almost same example and it works just fine but not this one But it works for any one of the fields just by commenting all the other columns with out changing any code. When I uncomment all the lines then it complains.

[DotNet]

Just want to mention that the temp table I am using is part of the datasources of a list page. The temp table property is set as TempDB and it throws the error. If I change it to the InMemory table then I don’t get the above error but I can’t use the linkPhysicalTableInstance property on the temp table to assign the data. I am not sure if there is a way to change the table property from TempDB to InMemory and vice versa.

[MartinDrab]

Your changes had no effect and removing them has no effect either. If you want to avoid automatically-added fields, use prodFieldList.dynamic(false).

No, you can’t change the type of temporary table at runtime. What problem do you have with linkPhysicalTableInstance()?