Bug when sorting character fields that begin with a concatenated decimal

[TonyDB]

I have a data source that looks like this:

Select
FieldA || FieldB as FieldC
from myTable

FieldA is a decimal(2). Some values in the table are one digit. Some values are two digits.
FieldB is a character

I then make a grid widget with this data source. In that grid widget, I have the FieldC column set up with CNX's built in sorting feature, which lets me click the header of the field in order to sort it in either ascending or descending order.

When I fire up my app and sort FieldC in ascending order, the values with a two-digit FieldA appear towards the top (in ascending order). Once those values are done, then the values with a one-digit FieldA are shown (also sorted in ascending order). The descending sort is the same, but in the opposite order.

For some reason, it is acting like there is a space in front of FieldA, and then sorting numbers before spaces, instead of sorting by the first character in the string.

I have a temporary workaround for this:

Select
digits(FieldA) || FieldB as FieldC
from myTable

This workaround sorts correctly, but it would be cool if I could sort correctly without leading zeroes.

Thank you!

[robert.swanson]

Are you sure this is a NAB-specific problem?

As a quick experiment, I tried this in STRSQL:

create table deleteme/tonydb
(fieldA decimal(2,0) not null with default,
fieldB character(5) not null with default,
info character(10) not null with default)

insert into deleteme/tonydb values (1, 'ABC', '1ABC')
insert into deleteme/tonydb values (2, 'XYZ', '2XYZ')
insert into deleteme/tonydb values (15, 'XYZ', '15XYZ')
insert into deleteme/tonydb values (20, 'ABC', '20ABC')
insert into deleteme/tonydb values (3, 'HIJ', '3HIJ')

select fieldA || fieldB as fieldC, info
from deleteme.tonydb
order by fieldC

That statement returns the following:

FIELDC INFO
1ABC   1ABC
15XYZ  15XYZ
2XYZ   2XYZ
20ABC  20ABC
3HIJ   3HIJ
******** End of data ********

Next I tried this:

select digits(fieldA)||fieldB as fieldC, info
from deleteme.tonydb
order by fieldC

Which returns this:

FIELDC INFO
01ABC  1ABC
02XYZ  2XYZ
03HIJ  3HIJ
15XYZ  15XYZ
20ABC  20ABC
******** End of data ********

I get the exact same results when pasting these SQL statements into a NAB data source.

[TonyDB]

You are right. After testing that table on my end, I realized that nab can behave how I want it to, I just have to do this:

LPad(FieldA, 2) || FieldB AS FieldC

With this, it's showing the user a trimmed FieldC, but sorting based on the entirety of FieldC (which will have a space in front of one-digit values for FieldA).

Thank you for your time.