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
midrange.com
