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
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
IBM System iDB2SQL
Last Comment
Nesa Bojovic
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.
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
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
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?
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?
ASKER
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?
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?
WRKPTFGRP and look at Level associated with SF99710
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rzam8/rzam8fixinfocumpacklevel.htm
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rzam8/rzam8fixinfocumpacklevel.htm
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
ASKER
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 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.. :)
ASKER
I found what the issue is... and I fixed my problem