Re: Sorting file based on variable string

On 22-Jan-2012 05:04 , Dennis Lovelady wrote:

Besides that, the OP stated that they wanted to avoid their
current proposed solution that would "create an intermediate file,
with the Last Name as a separate field".

Ah, but both our solutions are guilty of that! I cannot anticipate
a solution (other than the OP's proposed "write a program to do it"
[paraphrased]) that would avoid that step.

To clarify, not to defend... What was the SQL solution guilty of?

Seems the definition of what exactly the OP wanted to avoid, "that step", was and is still lacking. I had inferred that the stated desire was to avoid a step of processing which would produce an intermediate copy of the data and that intermediate file would include the Last Name data in a separate field; i.e. I had not considered that the desire was merely to avoid writing a program. My interpretation of the actual quote noting the "only solution I have is to write a program to create an intermediate file, with the Last Name as a separate field" was that a program was already possible and acceptable to be written to effect whatever is desired; only that the temporary copy and extra field was an undesirable effect.

If "that step" refers to an intermediate copy [with an extra field for the Last Name data]... The SQL solution that I offered neither explicitly creates an intermediate file nor generates another field\column for the Last Name [a field which would later need to be removed or omitted]. That solution was left embedded in the script, although including some "extra" work to resolve my own issue with the 0x1C and having used descending sort to emphasize the effect with the sample data. So the actual solution [to be gleaned, as an exercise for the reader,] was the one SQL statement:

select TheField
from TheFile
order by
substr( TheField
, locate(x'1C' concat 'A2', TheField)+3
, locate(x'1C', TheField
, locate(x'1C' concat 'A2', TheField)+3) -1
)

Additional SQL that was shown in the script was either for setup or visualization of the sample data against which the collation rules would be applied. And the one SQL statement could be placed in a QMQRY or run from either STRQM or iNav database script followed by a request to SAVE DATA AS [or equivalent request], which would also avoid writing any program if indeed that were what the OP wanted to avoid. FWiW that select-statement as the as-result-table for a CREATE TABLE AS WITH DATA could even create the replacement [though externally described] for the unordered flat file; possibly instead, an INSERT INTO, if the ORDER BY is ever supported on the fullselect of an INSERT.

Admittedly the ORDER BY expression does effectively generate the Last Name as a field to enable sort or indexed ordering, but that data is never part of the result set. And whether there is a sort or index is, aside from some specifications and clauses or environmental attributes to influence the query optimizer, outside the domain of the requester; as well as being inconsequential, because the ORDER BY clause does the work without any need for an explicit intermediate result.

Regards, Chuck