SUBSTR in interactive SQL

[adam fisher]

After struggling for a while I seem to have found that the interactive SQL requires a space after a comma when using SUBSTR.

For example

   > SELECT SUBSTR[B](QQPARM,1,5)[/B] FROM QAQQINI
     TOKEN ,1 WAS NOT VALID.  VALID TOKENS: ) ,.
   > SELECT SUBSTR[B](QQPARM,1, 5)[/B] FROM QAQQINI
     TOKEN ,1 WAS NOT VALID.  VALID TOKENS: ) ,.
   > SELECT SUBSTR[B](QQPARM, 1,5)[/B] FROM QAQQINI
     ARGUMENT 2 OF FUNCTION SUBSTRING NOT VALID.
   > SELECT SUBSTR[B](QQPARM, 1, 5)[/B] FROM QAQQINI
     SELECT STATEMENT RUN COMPLETE
===>

Is this normal? I'm using V6R1M1.

[TedHolt]

Re: SUBSTR in interactive SQL

It runs fine for me without leaving spaces after the commas, Adam. I'm on 7.2.

[egami]

Re: SUBSTR in interactive SQL

Same here on 7.1. I don't know of anyone I can reach out to on 6.1, but you might look into whether one of the SQL session attributes is perhaps causing this?

[NeilD]

Re: SUBSTR in interactive SQL

Works OK on 6.1 ....


> SELECT SUBSTR(desca,1,5) FROM invp100a
SELECT statement run complete.

Neil

[B.Hauser]

Re: SUBSTR in interactive SQL

Since you are from France, I assume you are using a decimal comma for numeric values (and not a decimal point).
If you are using the comma as decimal separator (like in Europe) you need an extras space after the SQL separators, otherwise the numbers are interpreted as decimals.

Birgitta

[TedHolt]

Re: SUBSTR in interactive SQL

Since you are from France, I assume you are using a decimal comma for numeric values (and not a decimal point).
If you are using the comma as decimal separator (like in Europe) you need an extras space after the SQL separators, otherwise the numbers are interpreted as decimals.

Birgitta

This is good to know. I may very well run into this some day. Thanks, Birgitta!