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.


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 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].