× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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' */


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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

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

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.