Re: Is it possible to do this with key fields? -- MIDRANGE-L

× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.

The usual recommendation is a separate column with the sort value. Your SQL could use a CASE WHEN USER03 = ' ' THEN '0030' ELSE USER03 END -- this way you don't need to add any columns to the original table. Then sort on the derived columns.

SELECT USER03, VENDOR, CASE...END SORTER FROM YOURFILE ORDER BY SORTER

Vern

At 11:58 AM 6/12/2003 -0700, you wrote:

Have an unusual situation with a 15-character alpha field (USER03) that I need to turn into a key field in a Vendor file in a new logical file. It will hold the value of a 4-digit company number *or* it will be blank. The keys in the new logical will be USER03, then VENDOR#. Somehow, for the purposes of reading this logical in key sequence, I need treat a value of blanks and a value of '0030' the same, in essence, reading the file in "sequence" as follows:
USER03  VENDOR
'0020'  9877412
'0030'  1254478
'    '  2214778
'0030'  4174411
'0040'  1234567
I think I already know that this can't be done, but is there a way to play with the collating sequence or somesuch?

TIA, Dan

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.