× 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.



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 */
, ...


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.