On 13-Dec-2013 08:57 -0800, Stone, Joel wrote:
<<SNIP>> I ran it WITHOUT the CHAR and it ran to completion
error-free.
Due to the implicit casting to CHAR by the SQL, for the result of the
the concatenation operator.
I think that it ran with expected results too.
I would prefer to use WITHOUT the CHAR casting as it is simpler to
read and cleaner-looking.
Actually I would use DIGITS instead of using CHAR [or implicit CHAR]
casting. Unless there are multiple [ten+] sequence numbers within the
seconds component of the JOTIME, the [implicit] CHAR casting should be
fine. But to be the safest, DIGITS is the better choice. Basically,
the potential problem is that the expected collation of the following
parenthetical values (yymmddhhmm1, yymmddhhmm2, yymmddhhmm11) is
improper; i.e. (yymmddhhmm1, yymmddhhmm11, yymmddhhmm2) whereby sequence
number-2 orders *after* sequence number-11. That of course is a
potential problem for the MIN and MAX; i.e. the collation to establish
those is the same as for ordering with ORDER BY.
Is it problematic in your opinion to skip the CHAR scalar casting
and let the SQL engine "figure it out" as shown:?
Or can this lead to unexpected results?
As noted above... using [implicit] CHAR casting is potentially
problematic, and could lead to unexpected results. Using the TIMESTAMP
instead of the DATE and TIME values would do very well to further and
greatly minimize that potential.
I am hoping that even though I am mixing character and numerics, as
long as the "WHERE" matches the "IN", I am comparing apples to apples
so it is safe.
For testing equivalence they are fine, as matching expressions. It
is because the equivalence test is to the aggregate for which the
potential for improper collation of the sequence number presents a
problem; i.e. numeric values cast to character are *left-justified*
whereas use of DIGITS to cast numeric values to character left-pad with
zeroes.
Insert into jrnflb4af
select * from jrnflALL
where JOCTRR = 8232817
AND JODATE || JOTIME || joseqn) in
(select min (JODATE || JOTIME || joseqn)
from jrnflall
group by joobj, joctrr
union
select max (JODATE || JOTIME || joseqn)
from jrnflall
group by joobj,joctrr
)
order by joobj,joctrr, joseqn
The above modification left an extraneous right parenthesis before
the "IN".
Because the JOSEQN is not consecutive ascending, including that
column in the ORDER BY is probably not desirable.? That is, the query
has become [primarily] dependent on collation by the [effective rather
than the actual] timestamp. To avoid including the expression in the
ORDER BY, just include the three columns that define the character
expression; they will collate properly as their original data type. But
because the expression is being evaluated anyhow, and the ordering
likely will be done with a sort, ordering by the expression of just the
result set may be faster.
I had intended to mention use of DIGITS in my prior reply, but I
seemed to have forgotten. First I was going to mention that DIGITS cuts
off the two non-numeric characters for the JOTIME, making a CHAR(6)
result instead of a CHAR(8) result; where the CHAR(8) result always gave
two blanks of padding at the right\end for every value. Then I was
going to warn of the potential collation [thus selection] issue for the
values due to the left-justification of the numeric values within the
character result; i.e. and for this scenario, an issue only for the way
the JOSEQN was being used.
Insert into jrnflb4af
select * from jrnflALL
where JOCTRR = 8232817
AND JODATE || DIGITS(JOTIME) || DIGITS(joseqn) in
(select min (JODATE || DIGITS(JOTIME) || DIGITS(joseqn))
from jrnflall
group by joobj, joctrr
union
select max (JODATE || DIGITS(JOTIME) || DIGITS(joseqn))
from jrnflall
group by joobj,joctrr
)
order by joobj,joctrr, jodate, jotime, joseqn
I would probably change the program and the SQL to generate and use
an Output File (OutFile) Format for the Display Journal (DSPJRN) that
includes the JOTSTP column of type TIMESTAMP. Then replace the
references to both JODATE and JOTIME to just JOTSTP; but of course CHAR
casting of the TIMESTAMP rather than DIGITS.
midrange.com
