SQL ..

[ting2000]

I have a sql that I am using to pick up the highest version of the document number. Document number is a combination of doc_key, supplement_Seq, and reissue_seq
example doc_key = 1000, supplement_seq = 22, reissue_seq = 3
document number is 1000-22-3

sSQL = "select max(doc_key ||'-'||supplement_seq||'-' ||reissue_seq) DocId"
sSQL = sSQL & " from tbl_documents "

doc_key, supplement_seq, and reissue_seq are primary keys

The sql doesn't work correctly since I put a "-" as part of the result in the MAX function.
Example,

doc_key Supplement_Seq Reissue_seq
1000 5 0
1000 6 1
1001 2 0
1001 11 0

Correct result should have been 1001-11-0 but using the sql above I am getting 1001-2-0 as result since 2 comes before 1 (from the string 11-0 and 2-0)

I am not sure if the question is clear to you. Basically I need to have a sql that would work correctly to sort three fields and find the correct document number and show the result in ####-##-# format. I have tried the following sql and cannot get the correct result either

sSQL = "select max(doc_key)||'-'||max(supplement_seq)||'-' ||max(reissue_seq) DocId"
sSQL = sSQL & " from tbl_documents "

This sql would pick up the highest doc_key then combine with the highest supplement_seq and then the highest reissue_seq in the table which will not give me the correct result.

Example

doc_key Supplement_Seq Reissue_seq
1000 5 0
1000 6 1
1001 2 0
1001 11 0

correct result should have been 1001-11-0 but using the sql above, I get 1001-11-1 since 1001 is the max, 11 is the max and 1 is the max in all three fields. Hopefully it is not to complicated for you to see what I am trying to say. If you have any suggestions, please let me know. Thanks.

[BruceG]

What you need to do is format each part of the number (each field that makes up this key) in a uniform format. In other words, if supplement_seq can be two digits long, numbers from 1 to 9 should be formatted as "01" to "09". The SQL you are posting looks like the SQL Server flavor; I am more familiar with Access SQL, where you can have embedded VB functions. If you can use the Format$ function in SQL Server, your query would look something like this:

sSQL = "select max(format$(doc_key, ""0000"") "
sSQL = sSQL & "||'-'||format$(supplement_seq, "00") " _
sSQL = sSQL & "||'-' ||format$(reissue_seq, "00")) DocId" 
sSQL = sSQL & " from tbl_documents "

If you can't use Format$ in this version of SQL, look in the documentation or help for a similar function. Good luck.