My problem was this - I had a simple summary query which looks like
this:
SELECT SUBSTR(coaitm,1,13),SUM(COUNCS) FROM F4105 GROUP BY
SUBSTR(coaitm,1,13) ORDER BY SUBSTR(coaitm,1,13)
which worked perfectly on the U.S. development system.
However, when I attempted to run this SQL on one of our European
development systems [either as embedded SQL or as an ad-hoc query via
STRSQL], I received a syntax error like "Position xx Token ( was not
valid. Valid tokens: ) ,. " {I'm typing this from memory...]
Europeans mostly use a comma separator for numeric values and not a period.
When coding something like SUBSTR(COATIM,1,13), 1,13 not interpreted as
being 2 independent numbers, but as a single number, i.e. 1.13.
Just insert a blank after each comma separator in your SQL statements and it
will work on any machine independent whether the comma or period numeric
separator is used.
SELECT SUBSTR(coaitm, 1, 13),SUM(COUNCS)
FROM F4105
GROUP BY
SUBSTR(coaitm, 1, 13)
ORDER BY SUBSTR(coaitm, 1, 13)
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von CRPence
Gesendet: Thursday, 27.6 2013 00:43
An: midrange-l@xxxxxxxxxxxx
Betreff: Re: Best coding practices when using SQL
On 25 Jun 2013 22:47, TheBorg wrote:
I am posting the answer for a specific problem, but feel free to
contribute your ideas about the subject line.
My problem was this - I had a simple summary query which looks like
this:
SELECT SUBSTR(coaitm,1,13),SUM(COUNCS) FROM F4105 GROUP BY
SUBSTR(coaitm,1,13) ORDER BY SUBSTR(coaitm,1,13)
which worked perfectly on the U.S. development system.
However, when I attempted to run this SQL on one of our European
development systems [either as embedded SQL or as an ad-hoc query via
STRSQL], I received a syntax error like "Position xx Token ( was not
valid. Valid tokens: ) ,. " {I'm typing this from memory...]
Dynamic SQL, irrespective of invocation from any HLL or elsewhere, would
be impacted by the chosen SQL OPTION DECFMT in such contexts.
Static statements however, those already compiled, should be unaffected by
any change to the DecFmt; i.e. for static statements, the DecFmt should
matter only during the compile.
Figuring that it had something to do with the system decimal format
[QDECFMT], by playing around with the formatting of the code [placing
1 space after each of the commas within the SUBSTRING function],
SELECT SUBSTR(coaitm, 1, 13),SUM(COUNCS) FROM F4105 GROUP BY
SUBSTR(coaitm, 1, 13) ORDER BY SUBSTR(coaitm, 1, 13)
it then ran properly.
Note: The two scalar functions SUBSTR and SUBSTRING are *not* merely
synonyms. Thus like being careful to add a blank after a comma, one should
take care in referring explicitly to the function-name being referenced.
Also there is value in knowing that the syntax for the SUBSTRING scalar
includes /words/ as separators instead of the comma, and thus that form of
the supported syntax similarly avoids any issue for changing the DECFMT for
the SQL OPTION. Thus if the effect of SUBSTRING over SUBSTR was acceptable
[for Characters vs Bytes], then the following expression could replace the
use of SUBSTR in the above query:
SUBSTRING(coaitm FROM 1 FOR 13)
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzscasubstr.ht
m
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Built-in functions -> Scalar functions
_i SUBSTR i_
"The SUBSTR function returns a substring of a string.
-SUBSTR--(--expression--,--start--+-----------+--)------><
'-,--length-'
..."
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzscasubstring
.htm
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Built-in functions -> Scalar functions
_i SUBSTRING i_
"The SUBSTRING function returns a substring of a string.
-+-SUBSTRING--(--expression--,--start--+-----------+--)------+-><
| '-,--length-' |
'-SUBSTRING--(--expression--FROM--start--+--------------+-)-'
'- FOR--length-'
..."
Sadly, those doc references do not properly code the references using
commas to properly avoid the syntax-error problem :-(
Note: Because of that difference between SUBSTR() and SUBSTRING(), it is
not as simple to blindly change every occurrence of "SUBSTR(x,s,l)"
to "SUBSTRING(x FROM s FOR l)" as it is easy to change every occurrence of
the "||" operator to the "CONCAT" operator :-(
With a bit of searching, I found this reference in the the IBM i
documentation [true of *all* languages, not just RPG]:
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/cl/crtsqlrpgi.h
tm
<<SNIP>>
More generic documentation [not for just RPG SQL pre-compiling] can be
found at the following link, which also describes how to establish the
effect of the DECFMT of SQL OPTION in various interfaces. Most notably, see
the "If the comma is the decimal point, the following rules
apply:":
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzdatsep.htm
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Language elements -> Constants
_i Decimal point i_
"You can specify a default decimal point.
The default decimal point can be specified:
* To interpret numeric constants
* To determine the decimal point character to use when casting a
character string to a number (for example, in the DECFLOAT, DECIMAL,
DOUBLE_PRECISION, FLOAT, and REAL scalar functions and the CAST
specification)
* To determine the decimal point character to use in the result when
casting a number to a string (for example, in the CHAR, VARCHAR, CLOB,
GRAPHIC, and VARGRAPHIC scalar functions and the CAST specification)
The default decimal point can be specified through the following interfaces:
Table 1. Default Decimal Point Interfaces
...
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)
Parent topic: _Constants_
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzch2cons.htm
"
FWiW I [and others] have noted many times, about various SQL snippets
coded here, that have the potential to suffer from such problems, and often
will recommend that the extra space should be added in order that anyone
copying\pasting the sample SQL would not experience difficulties; i.e. they
would not have to edit the statement to avoid either the -104 or what would
likely be unexpected effects for whenever a syntax error will not be the
effect. For example:
http://archive.midrange.com/midrange-l/201002/msg00703.html
"... The blank after a comma is good practice to ensure that a statement
formed when using a session having the period as the decimal point, will
still be valid when pasted into a session using the comma as decimal
point\separator. FWiW it is courteous to add the blank when publishing
statements [e.g. to a newsgroup] so that when someone in another language
environment might want to perform the same SQL, they will not have to
/correct/ the statement to make it function without the syntax error."
http://archive.midrange.com/midrange-l/201108/msg00204.html
http://archive.midrange.com/midrange-l/201208/msg00538.html
http://archive.midrange.com/midrange-l/201212/msg01123.html
"... using concat vs || and using spaces after each comma to ensure
compatibility across languages ..."
http://archive.midrange.com/midrange-l/201304/msg01299.html
"... That source is now compatible also when the decimal separator is a
comma."
http://archive.midrange.com/rpg400-l/201202/msg00225.html
"... SUBSTR() was properly formed with the extra space following each comma,
to prevent a syntax error, irrespective of decimal point choice\setting."
So, it quickly became apparent to me that spaces DO matter when
formatting your SQL to work properly on systems having different
settings for QDECFMT, and now I need to review all of our code for
this "feature".
While most white-space in statements is ignored by the SQL, indeed the
space after a comma can be meaningful, if not even required, to effect
desirable results.
Again, only dynamic[ally generated] statements should need be checked for
this "feature". Static statements, while probably best-written to allow
copy\paste or pre-compile within any other language environment, need only
pass parse\syntax-check in the language environment in which they will be
compiled. Thus if your source-code is never shared with anyone who will
attempt to compile with SQL OPTION DECFMT=*COMMA then only code that both
uses dynamic SQL *and* the eventual run-time environment for those dynamic
SQL requests might use the SQL OPTION DECFMT=*COMMA need be reviewed for
possible revision.
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
