Re: Best coding practices when using SQL -- MIDRANGE-L

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