ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SUBSTR in interactive SQL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SUBSTR in interactive SQL

    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
    Code:
       > 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.

  • #2
    Re: SUBSTR in interactive SQL

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

    Comment


    • #3
      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?

      Comment


      • #4
        Re: SUBSTR in interactive SQL

        Works OK on 6.1 ....


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

        Neil
        Bloke

        Comment


        • #5
          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

          Comment


          • #6
            Re: SUBSTR in interactive SQL

            Originally posted by B.Hauser View Post
            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!

            Comment

            Working...
            X