Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    18

    Unanswered: MAX and Left functions behavior...

    In a table I have a field "Ticket No.". I am using Ticket No in a format like:

    00021-10-06-0201

    The first set (left side) represents Ticket No. Then month, year, and code.

    Each time when a user wants to enter new record a new ID is generated as:

    "Select max(Left(TicketID,5)) from Ticket".

    The new id becomes result+1.

    I want to know that the above field is a string. But when I use LEFT
    function, as above, it successfully returns max of the first set. Does
    it automatically converts it to Long integer?

    If not, how to use it securely so that first the left set is converted
    to Long Integer and then the MAX is found. All in SQL query.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It is called the implicit conversion. Your RDBMS is clever enough to convert numeric part of a string to a number. However, it is much better to take it under YOUR control and use proper function to do that (I'm unfamiliar with database you use and can't suggest exact function name).

    Also, I believe that creating ID's (which should be, I presume, unique) with the "MAX + 1" technique works good only in a single-user environment. It will fail sooner or later in multi-user environment.

    In other words: what will you do when two users at the same time calculate MAX + 1 and use it in the transaction? One (faster) of them will successfully commit data, but another one (or more of them) will fail to do that because UNIQUE KEY CONSTRAINT will be violated.

    I'd suggest you to use another approach - in Oracle, it would be a sequence. In your database, it would perhaps be something else, but I believe there *must* be something like that (autoincrement column or ... whatever its name is).

  3. #3
    Join Date
    Feb 2005
    Posts
    18
    Presently I am using MS Access for my desktop application. It will not work in multiuser environment. Even if it in future, the last part 0101 contains the computer code. Even if the first part remains same for ID the last part will become 0102.

    Hence the strings may look like:

    00025-12-6-0101 (for ID generated by first computer)
    00025-12-6-0102 (for ID generated by second computer).

    Though it is right that I need to control the implicit conversion to avoid any chance of failure.

    How the query can be modified for MS Access.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the smart aleck answer is to forget what you are doing at present and redesign your DB
    you are making likfe very complicated by wrapping up a whole lot of data into a single column
    Id suggest you break it down into its contstiuent elements and (if required recreate the composite element for reporting or forms.. bu tstore the data at its atomic level

    eg
    ticket No
    date of (...what issue, validity)
    ComputerID

    computer ID is available from an API call... searh google for DEV ASHISH's API calls... there are some whizzy tools within later versions of Access to do this, but I prefer to stick tot he tools I know & trust

    is it valid to have the same ticket number allocated by two diffrent computers... sounds a bit odd but y'never know

    The task you have is a variation on the next available number problem... there are solutions posted in this thread (usually they involve table OR row locks).

    however going on the basis that your current desing is appropriate
    you may be able to get waht you want using a mix of LEFT & RIGHT parts of the exisitng query

    select MAX(CINT(left$(myticketnumber),5)) where right$(myticketnumber,11,4)="0101"

    personally I think its bobbins
    1 you are limited to 99999 tickets per computer
    2 you are limited to year 2009, unless you do a bit of nifty recoding so 2010 is 0, 2011 is 1....
    3 you are limited to a 4 digit computer ID
    ..but its your system

    as a general rule Id suggest if it looks very difficult to achieve what you wnat in SQL then your design is probably flaky. someitmes queries can be complex ecause of the interrealtions (joins) between tables. however on what is essentially a simple task (get next number) if its difficult then your desing may benefit from a rethink

    if you store the data separately it becomes
    select MAX(ticketNo)+1 as NextTicket from mytable where ComputerID="0101";
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2005
    Posts
    18
    healdem,

    You are right in that breaking the TicketNo into two more parts. This will improve query performance also. The changes you suggested can be done in the new implementation next year. For present installation, users have entered a lot of data. I cannot take much risk now.

    For the changes you suggested in my existing query, instead of CInt, is there something like CLongInt in MS Access.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by rpk2006
    ......For the changes you suggested in my existing query, instead of CInt, is there something like CLongInt in MS Access.
    have you tried the help system in Access?
    why do you think you will need to use data type long for a 5 digit integer/
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2005
    Posts
    18
    I think 99,999 will create problem for a 32,673 storage space.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •