Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
Obtaining next 'number' from a string field |
Tue, Jul 11 2017 9:05 PM | Permanent Link |
Adam H. | Hi,
I have a database I'm developing for a client who has a reference number for their table which will normally be a numeric value, but from time to time needs to allow for alphanumeric values. Such as: 1001 1002 1003 1003 1003B 1004 1005 ...etc, I'm needing to find out what the next number is going to be when creating a new record. Coming from a DBISam background in the past I've had a second column called 'SORTORDER' in which I've populated only the numeric value of the reference number in my delphi project, and I grab the last value from there and move on. The next number will always be a next full number. The appending of 'B' on the end is done manually by the user who can override the 'next number' if they choose to. As I'm developing this new small project in EDB I know there's a whole new world available to me in how to handle this. I'm aware that I can have caluclatedfields within EDB, and that I can also now use stored procedures. However I have no idea which would be the better approach, or where to start and I'm just looking for some ideas or advise to point me in the right direction to start with? Thanks Adam. |
Wed, Jul 12 2017 3:23 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
My first thought is why not two columns - reference & variant? You can then have a calculated column joining the two together for print / display. It also makes it (in my opinion) easier for the user to create the variant since its just filling in an extra field rather than over typing Roy Lambert |
Wed, Jul 12 2017 2:08 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< The next number will always be a next full number. The appending of 'B' on the end is done manually by the user who can override the 'next number' if they choose to. As I'm developing this new small project in EDB I know there's a whole new world available to me in how to handle this. I'm aware that I can have caluclatedfields within EDB, and that I can also now use stored procedures. However I have no idea which would be the better approach, or where to start and I'm just looking for some ideas or advise to point me in the right direction to start with? >> Well, you could certainly set this up as database function that does all of the underlying work, and then call this function as part of the default expression for the column. Or, if you don't want to "waste" numbers, then calling the function as part of a before insert trigger would suffice. Just make sure that your function that handles this uses a transaction around retrieving and updating the last number, and make sure that its a restricted transaction that only involves the table where your last number is stored. Tim Young Elevate Software www.elevatesoft.com |
Sun, Jul 16 2017 6:00 PM | Permanent Link |
Adam H. | Thanks Roy and Tim for your replies...
I've thought of having the 2 seperate columns - however I'm not sure at this stage exactly which direction my clients want to go with their numbering system. In the pasts they've 'changed their mind' from going numeric, to numberic with an alpha appendage, to having a suffix as well. (ie, P1001). The problem then was that when they reached P10001 the sorting was like: P1001 P10001 P10001B P10002 P1002 P1003 ... etc. Plus I'd need 2 columns, one for a suffix and one for a prefix. I'm beginning to think that adding two seperate columns (prefix and suffix) might be easier for me to work with even though the client's just wanting one field at this point in time. I'm still just looking into all the options that are available for me with EDB to try and find the best solution. Thanks for reminder/tip about transactions Tim! Greatly appreciated. Best Regards Adam. |
Mon, Jul 17 2017 3:08 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
Actually no you'd still only need the two columns plus a computed column. Since you can define just what you want OrderNumber: integer OrderAlpha: CHAR(2) OrderComputed: SUBSTR(OrderAlpha,1,1)+CAST(OrderNumber AS VARCHAR(10))+SUBSTR(OrderAlpha,2,1) Or something like that. You'll need to make sure that the OrderAlpha column is always left / right padded Roy Lambert |
Tue, Jul 18 2017 1:18 AM | Permanent Link |
Adam H. | Thanks Roy!
When it comes to computed fields, if I was to do a query or a sort on this field - would it hit me with performance as it's calculating each time - or is EDB optimised so that it wouldn't make any differences if I was running queries on Computed fields, vs real fields pre-populated and with an index? Cheers Adam. |
Tue, Jul 18 2017 3:17 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>When it comes to computed fields, if I was to do a query or a sort on this field - would it hit me with performance as it's calculating each time - or is EDB optimised so that it wouldn't make any differences if I was running queries on Computed fields, vs real fields pre-populated and with an index? I'll defer to Tim for a proper answer There are two types of calculated columsn in ElevateDB - COMPUTED and GENERATED. The difference is that COMPUTED aren't stored and are calculated on the fly - think OnCalcFields in a TEDBTable, but it will only be calculated if you refer to the column. GENERATED are stored so its more like a proper column and takes up disk space. Its only refreshed, recalculated, when the used data (columns) changes. There's another difference (if I've got i right) COMPUTED are calculated on the client so may involve additional network traffic. GENERATED can be calculated on the server. Generally speaking I've found that queries / filtering tables fall into one of two categories - "to damn slow "or "I don't notice" and I haven't found any difference wether I'm including COMPUTED or GENERATED columns. Mega queries / table filters or a heavily loaded or slow LAN may have an impact. Sorry I can't answer that. Roy Lambert |
Tue, Jul 18 2017 10:41 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< When it comes to computed fields, if I was to do a query or a sort on this field - would it hit me with performance as it's calculating each time - or is EDB optimised so that it wouldn't make any differences if I was running queries on Computed fields, vs real fields pre-populated and with an index? >> The way that computed columns work is this: 1) Computed column expressions are only evaluated on-demand when the column is read. 2) Any time any column that is referenced in a computed column expression is modified, EDB will flag the computed column so that it is re-evaluated the next time it is read. 3) Computed column expressions that do *not* reference other columns in the row (say, function calls without any column parameters) are only evaluated once after the row is buffered and the column is first read. The way around this is to pass any columns that you want to reference as parameters to the function so that 2) becomes effective for re-evaluation. So, when a computed column is evaluated is a function of how often the row containing the column is read and buffered, combined with whether the computed column has been read yet. But, the main take-away should be that computed columns are evaluated on-demand. If you want a calculated column that is as close to possible as a real column, then you really want generated columns. They are handled like real columns in terms of storage, etc. but are only re-evaluated when any referenced columns are modified and the row is actually inserted/updated. Tim Young Elevate Software www.elevatesoft.com |
Tue, Jul 18 2017 6:58 PM | Permanent Link |
Adam H. | Excellent - thanks for the clarification guys!
|
This web page was last updated on Wednesday, May 15, 2024 at 08:40 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |