On 30-Jul-2016 08:20 -0500, Carel wrote:
Op 29-7-2016 om 16:29 schreef Hoteltravelfundotcom:
[…]
CAST(CONVERT(VARCHAR, `OEINH2`.`IHDOCD`) AS DATE)
[…]
Why do you use SQL Server syntax, as there is an DB2 SQL Reference
manual online?
If you had used the right manual, you would have noticed that
CONVERT is not the right syntax.
There is no CONVERT scalar-function documented as a built-in for the
DB2 for i SQL, nor is there a CONVERT-specification\expression
documented as available; presumably, to what Carel alludes. And per
reference to the manuals for documentation, CONVERT can be found
documented in the most up-to-date DB2 for i SQL online reference…
*Neither here*, as a scalar:
IBM i 7.3->Database->Reference->SQL reference->Built-in
functions->Scalar functions
[
http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/db2/rbafzscale.htm]
*Nor here*, as an [syntax-]alternative for a cast-specification:
IBM i 7.3->Database->Reference->SQL reference->Language
elements->Expressions
[
http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/db2/rbafzch2expr.htm]
However, entirely FWiW TWiMC, the _syntax_ issue is another matter
entirely, for the reference to the CONVERT from the OP. A reader of the
OP can not presume to know whether the OP might have a User Defined
Function (UDF) named CONVERT, for which the syntax of that expression
remains *entirely valid*, given a correction is made for the
identifier-delimiters:
If the token VARCHAR referenced in the CONVERT() function portion of
the CAST-specification\expression is an identifier [e.g. variable-name
or column-name], then there is nothing _syntactically incorrect_ with
that expression:
IBM i 7.1->Database->Reference->SQL reference->Language elements
[
http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzsqlelem.htm]
The reference to a CONVERT function need not be a reference to a
supported DB2 for i SQL built-in scalar function to be valid; i.e, the
reference remains valid syntax for invocation of a scalar function:
IBM i 7.1->Database->Reference->SQL reference->Language
elements->Functions->Function invocation
[
http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzfuninv2.htm]
That is easily-enough verified as proper syntax, via the feature of
the Start SQL Interactive Session (STRSQL) having specified the *SYN
special-value specification for the Statement Processing (PROCESS)
parameter to request that the "statements are syntax checked only", upon
entry into a new SQL session; else by effecting same capability, via the
F13=Services within an existing\reused interactive session. The above
expression as parsed by the DB2 for i SQL, after having changed the
disallowed "`" characters to be [replaced by] the '"' character as the
allowed character for delimiting the [conspicuously and legitimately
intended-as] identifiers, will pass a syntax-check; e.g. first without
replacing the invalid delimiters, then after replacement, the effect is
shown as a comment line just after the VALUES request:
VALUES(CAST(CONVERT(VARCHAR, `OEINH2`.`IHDOCD`) AS DATE))
-- msg SQL0104 Token ` was not valid. Valid …" is the result
VALUES(CAST(CONVERT(VARCHAR, "OEINH2"."IHDOCD") AS DATE))
-- msg SQL6070 "Syntax check found no errors." is the result
midrange.com
