Solved

Combination of dense mode and insert mode

  • 17 June 2021
  • 2 replies
  • 57 views

Badge

Hi,

when writing to a database, is there a combination of “insert mode” and “dense mode”? I’m looking for something which does not delete the complete database table, but still writes data for rows where all parameters are empty.

Benedikt

icon

Best answer by Chris Kuip 22 June 2021, 16:00

Hello Benedikt,

 

Indeed, the combination insert mode and dense mode is not supported.

Perhaps a long shot, but using indexed database tables might provide an alternative.

Let me explain using the following example:

Data is kept per day, per product, per region. This data is updated monthly. So when writing the data, we want to update all data for that month, but leave the data for other months alone.

The database table is declared something like this, note that it is indexed per day.

DatabaseTable db_tableSelectedDay {

    IndexDomain: i_day;

    DataSource: sp_datasource;

    TableName: "TableAB";

    Mapping: {

        "pr"    --> i_product,

        "reg"   --> i_region,

        "date"  --> i_day,

        "price" --> p_price( i_product, i_region, i_day ),

        "sold"  --> p_sold( i_product, i_region, i_day )

    }

}

 

Next, when updating for a month, we use the following for loop:

Procedure pr_writeSelectedMonthDataToDatabase {

    Body: {

        

        for i_sday do

            write p_price( i_product, i_region, i_sday ), p_sold( i_product, i_region, i_sday ) 

                to table db_tableSelectedDay( i_sday ) in dense replace mode ;

        endfor ;

        

        CloseDataSource( sp_datasource );

    }

}

 

Does this provide an acceptable alternative?

 

With kind regards,

 

Chris

View original

2 replies

Userlevel 4
Badge +2

Hello Benedikt,

 

Indeed, the combination insert mode and dense mode is not supported.

Perhaps a long shot, but using indexed database tables might provide an alternative.

Let me explain using the following example:

Data is kept per day, per product, per region. This data is updated monthly. So when writing the data, we want to update all data for that month, but leave the data for other months alone.

The database table is declared something like this, note that it is indexed per day.

DatabaseTable db_tableSelectedDay {

    IndexDomain: i_day;

    DataSource: sp_datasource;

    TableName: "TableAB";

    Mapping: {

        "pr"    --> i_product,

        "reg"   --> i_region,

        "date"  --> i_day,

        "price" --> p_price( i_product, i_region, i_day ),

        "sold"  --> p_sold( i_product, i_region, i_day )

    }

}

 

Next, when updating for a month, we use the following for loop:

Procedure pr_writeSelectedMonthDataToDatabase {

    Body: {

        

        for i_sday do

            write p_price( i_product, i_region, i_sday ), p_sold( i_product, i_region, i_sday ) 

                to table db_tableSelectedDay( i_sday ) in dense replace mode ;

        endfor ;

        

        CloseDataSource( sp_datasource );

    }

}

 

Does this provide an acceptable alternative?

 

With kind regards,

 

Chris

Badge

Hello Chris,

thank you for your example. I haven’t used indexed database tables before and they seem to be an interesting alternative. I’ll be able to use this for my problem.

Best regards,

Benedikt

Reply


Didn't find what you were looking for? Try searching on our documentation pages:

AIMMS Developer & PRO | AIMMS How-To | AIMMS SC Navigator