Sorting in Crosstab

[gatorfe]

We have a crosstab report that is being sorted by the product code description.  The sql db in relational.  We are not seeing consistent behavior in the output though.  A pic is attached.  The 1st two columns represent specs on the product code. The 3rd column in the product code description and the last is the product code.  We can't understand why values for 4.173 and 4.646 sort correctly but not 5.196 and 5.512.  Any suggestions would be greatly appreciated.

[MFGF]

We have a crosstab report that is being sorted by the product code description.  The sql db in relational.  We are not seeing consistent behavior in the output though.  A pic is attached.  The 1st two columns represent specs on the product code. The 3rd column in the product code description and the last is the product code.  We can't understand why values for 4.173 and 4.646 sort correctly but not 5.196 and 5.512.  Any suggestions would be greatly appreciated.

Hi,

It's difficult to make sense of what you wrote here. The image you posted seems to show normal alphabetic sorting being done - what exactly is the problem?

For example, within 5.196, TP 316L SANMAC 132 X 106 MM HB appears before TP 316L SANMAC 132 X 90 MM HB. This is the correct result if you are sorting them in ascending order. The descriptions are the same up to the end of SANMAC 132 X, and then 1 comes before 9 in the alphanumeric ordering sequence.

Cheers!

MF.

[gatorfe]

Thank you for looking at this.  Under 5.196 the user wants 90 to show up 1st and then 106.  The same for 5.512 they want 80 to show up first and then 112.  Is there any way to do that?

[MFGF]

Thank you for looking at this.  Under 5.196 the user wants 90 to show up 1st and then 106.  The same for 5.512 they want 80 to show up first and then 112.  Is there any way to do that?

Not if you are sorting alphabetically. The current result is a correct alphabetical sort.

The way I see it, you have two choices:

1. Modify the descriptions so that the numbers always have the same number of digits, ie TP 316L SANMAC 132 X 090 MM HB
2. If the numbers always differ in a set place, extract the numbers into a different query calculation using substring() to locate them and cast() to convert them to numbers. Sort First on the description up to where you extracted the number, then within this sort on the number.

Neither is going to be an easy task for you.

Good luck!!

MF.

[gatorfe]

Thank you!