On 16-Oct-2015 17:24 -0500, Kendall Kinnear wrote:
I'm stumped by something a programmer brought up to me a little bit
ago. (5pm on Friday, just what I needed!)
I have an SQL statement that is coded as:
DATEG>=(left(cast(current_date as varchar(10)),4)
Seems a right-parenthesis was dropped; assumed that was there.
The casting from date-to-character, for intentions other than
displaying the result to humans [i.e. in computation\expressions],
*always* should be performed with the CHAR scalar casting function,
*never* the generic CAST scalar function.
That is because the CHAR scalar allows specifying the /format/ of the
resulting character string, whereas the CAST does not; specifically, the
CHAR casting scalar allows a second argument, in which a value such as
ISO, EUR, USA can be specified to ensure the desired format is effected
for the formatted date-string. The specified format is always the same
result-format, irrespective the SQL OPTION for DATFMT, whereas with the
CAST scalar, there is no argument to specify the DATFMT, and the
result-format is restricted to that of the SQL Date Format (DATFMT)
setting\OPTION established for that SQL /session/.
That statement works fine when I run the SQL statement using the
iNav run SQL function.
Presumably DATFMT=*ISO [or another of the 10-character, 4-digit-year
formats] is established for the result-format of date-string values.
When I try to run the same statement using RUNSQLSTM I get the
following which fails:
DATEG>=(left(Cast(Translate(Concat(Substring(Cast(CURRENT_DATE AS VarChar(10) CCSID37),1,4)
That is not the full message details, just a snippet of helpful
information that the database offered; an expansion of the expression
and predicate. The full details are [typically] required to be included
for the messaging, for a reader to properly assist in diagnosing the
problem. This issue however, is so common, and unlikely to be anything
else than...
For some reason RUNSQLSTM thinks it needs to translate CURRENT_DATE
to CCSID 37 and inserts the appropriate code.
The DATE data type is, given the high dependency on _character_
representations, is required to have a CCSID assigned, despite there may
seem no need; that is to suggest, that if the same rules applied to
numeric data, one might expect the numeric fields also would have to
have a CCSID assigned, but they do not. However the /editing/ feature
does have to have a CCSID. Date data type data however, ¿for reasons of
expediency and\or distaste by some for the concept of the EDIT
instruction? I do not know, but accept that that is a requirement. That
said, I there is almost zero chance of there being any relationship
between the CCSID casting and the issue being experienced.
I am very far from being an SQL expert so can someone point me in
the right direction?
The DDL was not given, nor the full message details of the msg
CPF5035 [or similar] that offered up the failing predicate. However
quite conspicuously, the column DATEG is at least a 4-digit numeric data
type. Because the DatFmt for the Run SQL Statement defaults to *JOB,
and the Date Separator (DATSEP) also defaults to *JOB, the
left-justified date-string formatted-result is in the form of *YMD,
*DMY, *MDY, or *JUL, for which every one of them will include a
*separator character* such as dash, slash, period, or comma. And while
either of the last two can be valid [because either character could
appear to be a valid decimal separator to the implicit
character-to-numeric casting function], that would depend on the
established Decimal Point (DECMPT) for the decimal separator used in
numeric literals\constants.
So if the DATFMT(*JOB) resolved to OPTION of DATFMT=*MDY and
DATSEP(*JOB) resolved to OPTION of DATSEP='/', then the attempt by the
database SQL to compute a result from the expression [in October between
10-Oct and 19-Oct inclusive] to assign to a a temporary operand
for the comparison to the value in the column named DATEG, that would
have failed. The failure would occur because the character expression
would have to be implicitly cast into numeric, from the character-string
value of '10/1'. But because the casting is not [also] an
expression-evaluator of the character-string result, that
character-string value can not be CAST into a numeric value because the
slash character is not a digit [nor a decimal separator]; the expression
using the CAST scalar fails to assign a value for comparison with the
numeric column DATEG.
The better expression, given the resultant value of the expression
should be the four-digit year value, would be to use the YEAR scalar [or
revise the expression to use CHAR and the argument to name the
date-format; here are both:
(YEAR(current_date)) /* place inside ZONE or DEC casting scalar to
explicitly specify matching data type and size to the DATEG column */
(left(char(current_date, ISO), 4)) /* ISO=='YYYY-MM-DD' */
midrange.com
