Link to home
Start Free TrialLog in
Avatar of Nesa Bojovic
Nesa Bojovic

asked on

on iSeries problem with SUBSTR via SQL

Hi,

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
Avatar of Mark Murphy
Mark Murphy
Flag of United States of America image

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.
Avatar of Nesa Bojovic
Nesa Bojovic

ASKER

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.

Thanks,
Nesa
Avatar of Gary Patterson, CISSP
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?
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?
SOLUTION
Avatar of Gary Patterson, CISSP
Gary Patterson, CISSP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.. :)
I found what the issue is... and I fixed my problem