Skip to Content
author's profile photo Former Member
Former Member

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

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on May 25, 2005 at 08:41 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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");
      }
      

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.