Ordering using poorly formatted column data...

[holdemfoldem]

...I'm writing an app in VB 6 connecting to a main frame DB2 data base and wish to order the results of a select statement by two fields, SCHLYEAR and TERM, corresponding to the school year and the term, (ie: Fall, Spring, or Summer).

What keeps me from simply using:

"SELECT FIELD1, FIELD2, ..., FIELDN ORDER BY SCHLYEAR, TERM"

is that the TERM field is not well maintained. It is a 2 byte field which is supposed to have a "1" for Fall, "2" for Spring, or "3" for Summer, which it does. BUT the field may have any of the following values:
"01", " 1", "1 ", similar for "2" and "3".

This results in unreliable sorts because the first byte may be either a "0", " ", or "1, 2, or 3".

I've tried some suggestions by fellow staff, like case statements, etc, but keep getting non-descript SQL errors.

Can anyone help me build a SELECT statement so I can get an order based on the "1", "2", or "3" contained somewhere within each TERM field?

Thanks ahead for any help!

 

[checkai]

couldn't you order by

replace(convert(char(2),field),0,'')

dlc?

 

[SQLBill]

You might do better posting in an appropriate forum. This is for Microsoft SQL Server, not VB or DB2.

Just a suggestion.

-SQLBill

 

[SQLSister]

It would be in your best interest in the long run to fix this data and set up data entry controls to prevent this from happening again.