Best coding practices when using SQL

I 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...]

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.

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

[...]

Decimal point options:
*JOB
The representation for the decimal point specified for the job at precompile time is used.
Note: If QDECFMT specifies that the value used as the decimal point is a comma, any numeric constants in lists (such as in the SELECT clause and VALUES clause) must be separated by a comma followed by a blank. For example, VALUES(1,1, 2,23, 4,1) is equivalent to VALUES(1.1,2.23,4.1) where the decimal point is the period.

*SYSVAL
The value used as the decimal point in numeric constants is from the QDECFMT system value. This value is also used as the decimal point character when casting a numeric value to character.
Note: If QDECFMT specifies that the value used as the decimal point is a comma; any numeric constants in lists (such as in the SELECT clause and VALUES clause) must be separated by a comma followed by a blank. For example, VALUES(1,1, 2,23, 4,1) is equivalent to VALUES(1.1,2.23,4.1) where the decimal point is the period.

*PERIOD
The value used as the decimal point for numeric constants used in SQL statements is a period. This value is also used as the decimal point character when casting a numeric value to character.

*COMMA
The value used as the decimal point in numeric constants is a comma. Any numeric constants in lists (such as in the SELECT clause and VALUES clause) must be separated by a comma followed by a blank. For example, VALUES(1,1, 2,23, 4,1) is equivalent to VALUES(1.1,2.23,4.1) where the decimal point is the period.
[...]

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

;-)

- sjl