Select with aggregate functions? i.e. select max(code)...

[Former Member]

The code field on my user table is too small for the key field I want to use but it is still necessary I assume I must generate my own unique value.

The DBDataSource.Query() method doesn't seem appropriate so should I create a SAPbobsCOM.Recordset and use the DoQuery method to select the maximum value currently in the user table?

I found some mentions of requiring such a select statement on this board but no examples of the actual code. I'll go ahead and try the DoQuery method unless I hear of something more appropriate.

Also, I saw a comment that a future release of SAP would have a larger code field for user tables but obviously that didn't happen in 6.7.

Thanks for any comments.

Bill Faulk

[Former Member]

I wanted to note that user tables in my demo database, such as @BBB, use an 8 character hexadecimal value from 00000000 to FFFFFFFF.

Also, it's always possible to store the next value in a table and increment that instead of using the MAX() function but that means adding another user table.

[Former Member]

Here's what I came up with...

If you are happy with 99,999,999 records instead of 4,294,967,295 then I guess you can forego the hexadecimal bit. I'd welcome any alternatives if someone has one.

With the hex:

private string GetNextKey(string strTable)
{
    SAPbobsCOM.Recordset rs =
        (SAPbobsCOM.Recordset)oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
    rs.DoQuery("select isnull(max(code),'00000000') from [" + strTable + "]");
    string strCode = (string)rs.Fields.Item(0).Value;
    int intKey = Convert.ToInt32(strCode,16) + 1;
    return intKey.ToString("X8");
}

No Hex:

private string GetNextKey(string strTable)
{
    SAPbobsCOM.Recordset rs =
      (SAPbobsCOM.Recordset)oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
    rs.DoQuery("select isnull(max(code),'00000000') from [" + strTable + "]");
    string strCode = (string)rs.Fields.Item(0).Value;
    int intKey = Convert.ToInt32(strCode) + 1;
    return intKey.ToString("00000000");
}