Using the BETWEEN keyword

[aplusc]

I have a table with these two columns:

transaction_code, transaction_description

I need to have a way of selecting a RANGE of transaction codes from from this table. So for example, you would input 'A' as the starting code and 'D' as the ending code to get all the 'A','B','C','D' records. So naturally I would write something like this:

select transaction_code, transaction_description from tran_table where transaction_code BETWEEN @tran1 and @tran2

However there is a catch. There are also transaction codes which are numeric (i.e. 1,2,3,5...9). Not only that, but numeric codes come AFTER the alphabetic ones. Therefore, in some cases I could have 'X' as the beggining code and '3' as the ending code. Which is suppose to return 'X','Y','Z','1','2','3'. Obviously, the statement -

select transaction_code, transaction_description from tran_table where transaction_code BETWEEN 'X' and '3'

- will not work, because 'X' is greater then '3' and the range is invalid.

There is no way around this, and I also cannot use two ranges (ie X-Z and 1-3) for reasons too long to explain. Does anyone have any ideas?

Thanks!

 

[grega]

Check @tran1 and @tran2 before issuing the select. If @tran2 < @tran1, swap the values around.

Greg.

 

[JMCraig]

It sounds to me like Greg's idea would work all right, but I'm very curious about this part of your statement:

&quot;Not only that, but numeric codes come AFTER the alphabetic ones.&quot;

Do you mean:

By convention and usage by people, the numeric codes are considered to be greater than the alphabetic ones?

The table is somehow clustered (on another column) so that these values are later in the table?

If I've approximated your meaning with my first question (regarding convention and usage by people), then one solution (fairly complex, but very flexible) is to create another column that contains the weighting (probably a number) of the entry in usage. E.g.:

[tt]
transaction_code transaction_code_ord ....

A 0
B 1
C 2
...
Z 26
0 27
1 28
...
[/tt]

You might want to start with 10 and go up by 10's in case you ever need to add new values later (although you can always just renumber the entries if you add more--at least in the approach I'd suggest). Then you could do something like this:

declare @startOrder smallint, @endOrder smallint, @swapOrder smallint
select @startOrder = transaction_code_ord
from tran_table
where transaction_code = @tran1
/* assumes @tran1 is as you've shown in your original post */
select @endOrder = transaction_code_ord
from tran_table
where transaction_code = @tran2

/* swap values if needed */
if @endOrder < @startOrder begin
select @swapOrder = @endOrder
select @endOrder = @startOrder
select @startOrder = @swapOrder
end
/* get the data */
select * from tran_table
where transaction_code_ord between @startOrder and @endOrder

The addition of this column would have a side benefit: if people really want to see the transaction codes ordered with the numbers after the letters, you'd now have a target for an ORDER BY so you could retrieve the data according to usage convention.

BOL,

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[aplusc]

JM Craig, you are exactly correct. The transaction papers are PHYSICALLY ordered this way for business purposes. Certain transaction papers are grouped in folders and this is why there is a need to use ranges as the could be hundreds of papers in a single folder.

Your solution would work, unfortunately I lied a little bit when I mentioned that the transaction codes are only one character, to make the problem easier to understand. In fact the codes are 8 characters long and any of the 8 characters could be a letter or a number.

Therefore to implement your solution I would have to either:

a) Perform this check on every single character.
b) Store a sort order for every single possible transaction code, of which there are over a 1,000,000.

Both of these solutions will take up a lot of resources to implement.

However I'm trying to find other alternatives. For example, if you run &quot;sp_helpsort&quot;, it will display your default sort oder. I am wondering how I could change this. (The sort odrder or collation, affects all statements realted to sorting and comparing strings.

Then there is also the &quot;SORTKEY&quot; and &quot;COMPARE&quot; functions which work with different collations. If anyone knows how I could use them for this that would be great.

 

[JMCraig]

You can change a server's sort order, but since this is a global change, I wouldn't recommend it.

I don't have time to do the bit of research I'd need to do to refresh my memory about SORTKEY and COMPARE (which we decided not to use on a project some years back and I can't recall all the reasons). They might well work for you.

However, creating your own sort key isn't as hard as you might think (you mentioned this would require):

a) Perform this check on every single character.
b) Store a sort order for every single possible transaction code, of which there are over a 1,000,000.

How about setting up a little table with the right collation for each of the constituent characters (A-Z and 0-9, right?) and then building the sort key with a script:

Your sortkey becomes a varbinary and you create a loop that uses a substring function to look at each character, find its weighting and append the appropriate byte to the sortkey (e.g. according to the translation table I proposed above). Now, you could just stick this 8-byte code on each transaction or you could have a table that correlates the codes to the ordering. Clearly this'll take up some space, but it's one way to go and it may be simpler than trying to deal with a sortkey created by Sybase. One of the problems with using sortkeys generated by generic algorithms is that they generally provide for a lot larger of a sorting domain than you need (and this is certainly true in your case where the characters in question are small). That is, you sometimes end up with 6 bytes of sort key data per character--huge overkill (you only need 7 bits per character to handle the range you need).

That said, the ability to do a SORTKEY in-line would simplify things considerably and you may be able to configure your collation so it doesn't get silly with the number of bytes of binary data returned as the sort key. (This is one of those times when having Java in-the-DB capability would make it all so easy--it's really too bad Sybase charges so much for that add-on.)

There's some info on alternate collations in the Install/Config guide (Chapter 5 for version 12). It appears they expect you to use collations that Sybase makes available. You used to be able to take one of the files that defined the sort orders (.srt extension) and edit them with a text editor and adjust them accordingly--the last I knew, you could still do that, but I haven't done in myself since 4.2 (gasp!)--I know it worked up through at least the 11.x versions. The format is a little obscure and you may or may not be able to achieve what you want. I've done editing to force it to treat characters as equivalent and to move around punctuation marks. As I recall, the ordering used to be based on the ordering in the file (so presumably you could move the digits to after the numbers). I'm not sure whether you can create files that didn't come with Sybase and use them. There's an obscure reference to &quot;external&quot; collations and it says you have to use the file name--so I'd probably try it.

BOL,

John
John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net