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.

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

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

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.

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

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.

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

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

Excellent - thanks for the clarification guys!