The issue for portability\internationalization is with SQL parsing
when the "decimal separator" [AKA "Decimal point" and the at least
two\inconsistent parameter names DECPNT() and DECMPT(), possibly also
DECFMT() per system value name QDECFMT] is established as a comma
character versus the period character. Since the issue is with parsing,
compiled static SQL for which there is no expectation that the recipient
would need to compile from the original source, they would not
experience an issue because the statements were already parsed during
the pre-compile; i.e. anyone creating static SQL programs may distribute
the executable to run anywhere without concern for parser issues across
environments of diverse decimal separator.
Within an active STRSQL session, the F13=Services can be used to
change the choice of decimal-point to *COMMA, in order to mimic what a
user in another country [where their localization has a comma as a
decimal separator] would experience with dynamic SQL or attempts to
compile as embedded source statements. Note: Do not try to use STRSQL
DECPNT(*COMMA) if the user might already have an active session. If the
original static source or the original or generated SQL statement source
fails to include a space after the comma [as a consistent and easy
resolution] to prevent the parser from "seeing" a decimal literal
instead of comma separated literals, then usage in that other language
environment may fail or perhaps instead produce incorrect\unexpected
output; e.g. as with VALUES() or anywhere integers are not required on
either side of the comma as element delimiter.
http://archive.midrange.com/midrange-l/201002/msg00703.html
The example in the earlier message that I alluded might have an issue
for lack of required spacing, "cast(fldCC as dec(2,0))", would have
experienced SQLCODE -104, SQL0104 "Token 2,0 was not valid. Valid
tokens: <INTEGER>." if the statement text had been copied\pasted as SQL
source and parsed within an environment with comma as decimal separator.
There is some documentation specific to this concern, although not in
a localization\internationalization section:
"Decimal point"
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzdatsep.htm
" If the comma is the decimal point, the following rules apply:
* A period will also be allowed as a decimal point.
* A comma intended as a separator of numeric constants in a list
must be followed by a space.
* A comma intended as a decimal point must not be followed by a space.
Thus, to specify a decimal constant without a fractional part, the
trailing comma must be followed by a non-blank character. The non-blank
character can be a separator comma, as in:
VALUES(9999999999,, 111)
"
FWiW:
I personally have never understood why the SQL parser enforces that
strict interpretation of decimal token for language elements that are
almost certainly never going to change; e.g. those that will never
change to accept decimal versus integer literals. A parser can
easily-enough infer the intent of SUBSTR(FLD,2,2) with the knowledge
that the second and third elements\parameters\operands of the SUBSTR
scalar have and will always require integer values for literal\constant
specifications. Rather than any concern for some enhancement to allow
that [IMO more intuitive interpretation], I find the resolution of
"always" writing such expressions with i18n in mind is easy enough; i.e.
written as SUBSTR(FLD, 2, 2) instead. Some may notice that I do not
always follow that recommendation, specifically because I may write
SUBSTR(FLD,2,2) inline to text to avoid line-wrap issues. Placed on its
own line within reasonable line lengths, after many years doing so, I
almost subconsciously just add the extra spaces; e.g.:
select substr(fld, 2, 2) from filename
http://archive.midrange.com/midrange-l/200712/msg00817.html
Also FWiW:
The v6r1 docs are properly separated resources\pages such that a
functional direct-link can be obtained, and is effectively identical to
v5r4:
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscadec.htm
In a section "Note", the comment "Syntax alternatives: When the
precision is specified, the _CAST specification_ should be used for
maximal portability. For more information, see CAST specification."
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzcast.htm
The comma is the only supported separator between the three or four
elements\parameters of the DECIMAL scalar cast function, and between the
precision and scale elements of the "data-type" for CAST specification
[, and for CREATE TABLE, and for DECLARE in a compound-statement]. In
each case however, no comma is required because either all or any
additional elements are all optional.
The syntax diagram for DEC shows that if the precision is omitted or
neither precision nor scale are specified, then no comma need be
specified; that the default precision varies according to the type of
the [numeric or string] expression being cast, and the default scale is
zero.
Similarly for the CAST-specification syntax diagram, matching the
CREATE TABLE and compound-statement DECLARE for DECIMAL() "data-type"
variable declarations, but the default precision is five and the default
scale is also zero.
Regards, Chuck
On 05-Aug-2011 06:30 , Morgan, Paul wrote:
The V5R4 SQL Reference Manual SQL Syntax Diagram for Cast and Dec
require the comma. Is the comma optional or interpreted differently
with internationalization? If I'm going to write portable SQL code
how best to write it for i18n?
On 04-Aug-2011 18:09 , CRPence wrote:
<<SNIP>>
Note also the space included after the comma so the syntax remains
valid where decimal separator is comma. Including the blank space
after the comma increases the portability of the SQL; e.g. someone
in Spain could copy\paste the above statement snippet without then
also having to add the missing spaces to the quoted snippet, in
order to make the SQL functional. However for the special case of
no scale, per zero specified on each scale provided, then even
better to just drop both the comma and the scale specification
entirely, irrespective of the means utilized to effect the casting
to decimal; e.g. showing both forms of casting with zero-scale
defaulted:
select TRUEDATE( dec(fldCC, 2) /* scale defaults to zero */
, cast(fldYY as dec(2)) /*scale defaults to 0 */
, ...
midrange.com
