on iSeries problem with SUBSTR via SQL


I have a problem via SQL and into RPG too when I type SUBSTR(xxxx,1,6)
from some reason on my iSeries it requesting to be one space between comma and lengths e.g. substr(xxx, 1, 6).
I know that on other iSeries it's not a problem and on more examples it is not written with these spaces...

This is the error message when I check

Select * from ztrans0p                    
 where substr(trcrd#,1,6) = '516971'        
 Token ,1 was not valid. Valid tokens: ) ,.

Message ID . . . . . . :   SQL0104                                            
Message . . . . :   Token ,1 was not valid. Valid tokens: ) ,.                
Cause . . . . . :   A syntax error was detected at token ,1.  Token ,1 is not  
  a valid token.  A partial list of valid tokens is ) ,.  This list assumes    
  that the statement is correct up to the token.  The error may be earlier in  
  the statement, but the syntax of the statement appears to be valid up to    
  this point.                                                                  
Recovery  . . . :   Do one or more of the following and try the request again:
    -- Verify the SQL statement in the area of the token ,1. Correct the      
  statement.  The error could be a missing comma or quotation mark, it could  
  be a misspelled word, or it could be related to the order of clauses.        
    -- If the error token is <END-OF-STATEMENT>, correct the SQL statement    
  because it does not end with a valid clause.                                

Do you know where I can check what is wrong with my iSeries?

Thanks in advance,
Nesa BojovicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark MurphyIT ConsultantCommented:
Frequently an error in an SQL statement will appear far apart from the real error. For example, Is the statement immediately before the sql properly terminated? Maybe if you cut and paste 10 lines of code on each side of the SQL statement we can help more. This statement though should work as written.
Nesa BojovicAuthor Commented:
Unfortunately is not working if I write on the way that I haven't spaces.

This statement not working
Select * from ztrans0p                    
 where substr(trcrd#,1,6) = '516971'

If I add spaces it working
Select * from ztrans0p                    
 where substr(trcrd#, 1, 6) = '516971'

This is my problem, because we got some build from the SW vendor where they have in many programs embedded SQL statements without spaces and all programs are failing...
However on other machines which are not my iSeries it working correctly.
I assuming that some setup on the iSeries should be changed how it will allow substring without spaces.
Maybe will be good info too that on my machine I cannot use || for concatenation, I need to use concat.

Gary PattersonVP Technology / Senior Consultant Commented:
Well, the concat issue sounds to me like CCSID mismatch.  Do you have this problem in CL, too?

What is the CCSID of the source the vendor sent you versus the system CCSID, and the job CCSID where you try to compile?  What CCSID is your emulator session set for?

Dont really see how a CCSID mismatch could cause the subst issue though.  Just curious, do you have the same problem with a column name that does not contain a special character?

What OS release and cume PTF level are you running?
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Nesa BojovicAuthor Commented:
Hi Gary,

Yes, you are right about concatenate it depending of the CCSID.
Default CCSID is 870 however when I change on 65535 it working.

However still not working for substr.
I also checked Decimal Format it is "J" I tried with *BLANK but result was the same... not working

Yes, I have also issue for the column name which hasn't special charactes
SELECT * FROM zcdbsv0p WHERE substr(SVSRVD,1,7) = '0232002'
Token ,1 was not valid. Valid tokens: ) ,.                  

OS level is 7.1
How to see the PTF level?
Gary PattersonVP Technology / Senior Consultant Commented:
Gary PattersonVP Technology / Senior Consultant Commented:
Ok, since we have a CCSID issue elsewhere, let's focus on CCSID mismatch for a minute.  CCSID mismatches can cause some odd behavior, though I am having a hard time seeing how it might be causing this.  Still, I'd like to eliminate it.

Make sure that the system CCSID, job CCSID, and CCSID of any source member containing this SQL match.  If you are executing interactively from Run SQL Scripts, it shouldn't matter, Run SQL Scripts will usually handle CCSID conversion from Windows to IBM i for you.  If green-screen STRSQL, make sure your emulator session is configured for 870, and also make sure your interactive job is configured for 870.  Also, if you are cutting and pasting SQL statement, you might want to also try manually entering SQL statement in various interfaces.
Next, does this happen though all SQL interfaces, or just some?  For example:

Run SQL Scripts in Navigator
Embedded SQL in RPG program
STRSQL green screen

Also, does it happen with the substring() version of the function, or just substr()?

Finally, try running an SQL Performance Monitor to capture the SQL statement, and look at the statement text that is captured when the error is generated.
Nesa BojovicAuthor Commented:

I found what the issue is :)

it is problem that in system Val QDECFMT     *EDT     Decimal format had value 2 (2=J(Comma for decimal, one leading zero) )
When I changed on 1 (1=blank(Period for decimal, zero suppression))

Everything working correctly.

Thanks for all help.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary PattersonVP Technology / Senior Consultant Commented:
Good catch.  I can see how that could create an issue someplace in parsing a string with commas in it.  Thanks for sharing the solution.
Nesa BojovicAuthor Commented:
Each time, because, possible that someone else will have the same/similar issue and to know what to check.
I hate when somebody who open an issue don't close it w/o any info is it solved and how or just gave up from that issue.. :)
Nesa BojovicAuthor Commented:
I found what the issue is... and I fixed my problem
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.