Substrings work on “positions”, not on offsets. There is no position ’0′ in a string. Strings start with position ’1′. Use SUBSTR(PLGLDT,1,4), assuming that PLGLDT is a character column. If it’s a numeric column, you’ll need to convert it to character first. Tom

I gave substr(plgldt,1,4) but it says…
“Token ( was not valid.”

I tried substr(char(PLGLDT),1,4)…
but it says..
“Token ( was not valid.”

The As400 version that i use is V4… I think SUBSTR will not be supported in this version.. is there any other alternative?

I’d be extremely surprised if something as fundamental as SUBSTR wasn’t supported at V4? Besides, your original post containing the error text “Argument 2 of substringing function not valid” seems to suggest that it is? I would suggest a syntax error in the command somewhere. I believe it should read SUBSTR(CHAR(PLGLDT,1,4)) – your example below was missing a closing parenthesis. As an alternative you could try using SUBSTR(CAST(PLGLDT AS CHARACTER(8)),1,4) ?

Whoops sorry my bad, I’ll learn to read this time! Your second example does look correct. Still think there must be another syntax error somewhere. Try the cast and see if that makes any difference.

Hi,
   I tried the below query… still it shows “Token ( was not valid. Valid tokens: FOR WITH ORDER UNION OPTIMIZE.”
select SUBSTR(CAST(AMHTDA AS CHARACTER(0)),1,4) as year,
count(*) as “record count” from bpcsf60com/amh group by
SUBSTR(CAST(AMHTDA AS CHARACTER(8)),1,4) order by
SUBSTR(CAST(AMHTDA AS CHARACTER(8)),1,4)

Tried
select SUBSTR(CAST(AMHTDA AS CHAR(8)),1,4) as year,
count(*) as “record count” from bpcsf60com/amh group by
SUBSTR(CAST(AMHTDA AS CHAR(8)),1,4) order by
SUBSTR(CAST(AMHTDA AS CHAR(8)),1,4)
Still same error…
“Token ( was not valid. Valid tokens: FOR WITH ORDER UNION OPTIMIZE.”

Just a guess, YEAR is a keyword so you may not be able to use ‘year’ as a column name. Try changing it to something else and see if that works?

Odd, I just ran your  Jul 12, 2012  11:35 AM (GMT) statement.
The command worked with my data on V5r4
 

OR extracting the year mathametically worksselect int(AMHTDA/10000) as year,
count(*) as “record count”
from bpcsf60com/amh
GROUP BY int(AMHTDA/10000)
ORDER BY int(AMHTDA/10000)
Phil

but not the editor .. you just can’t put enough carrage returns into these discussions!!!

Hi, You can try double quote too “”.
select SUBSTR(CAST(AMHTDA AS CHAR(8)),1,4) as year,     count(*) as “record count” from bpcsf60com/amh group by SUBSTR(CAST(AMHTDA AS CHAR(8)),1,4) order by            SUBSTR(CAST(AMHTDA AS CHAR(8)),1,4)                     AMH in BPCSF60COM type *FILE not found.                
 

You can test the entire date via   > 20120000 and < 20129999

“Still same error…
“Token ( was not valid. Valid tokens: FOR WITH ORDER UNION OPTIMIZE.””There are 10 “(” symbols in your query.  Doesn’t the error message tell you which one is causing the error ?

“date field is in YYYYMMDD format.”
 
 Yes but what type is the field, numeric, char, date?

the date field is a packed field..

Then these answers should work.
As Carlos said, in interactive sql (STRSQL) the cursor should go to the position where the systems thinks there is an error.
Perhaps you hit an odd key around one of the ( .. retype the command

Just as a matter of interest, and to rule in the possibility of a fault with the SQL CLI, have you tried running the same statement using the RUNSQLSTM command?

…the possibility of a fault with the SQL CLI…
 
So far, there’s no indication that SQL CLI is being used (though it might be used behind the STRSQL interface. Regardless, trying the statement through RUNSQLSTM is a good idea.
 
If this really is as old as V4, I would suspect a PTF problem. And that would mean it’s not going to be fixed. Alternatively, V4 might not have recognized that expression in a GROUP BY and maybe an ORDER BY clause. I sure don’t remember those details.
 
Tom

Was there a problem with SUBSTR vs SUBSTRING in V4???

@philpl1jb – must admit I had the same thought last night myself, but then I looked at the original post again. The error “Argument 2 of substringing function not valid” seems to suggest SUBSTR is being recognised as a valid function. That said, is SUBSTRING in itself a different function with different underlying code I wonder? Needs to be tried..