STRSQL query

280 pts.
Tags:
AS/400
AS/400 errors
SUBSTR
I need to know how to get the record count for a particular year. the date field is in YYYYMMDD format. I tried the query select SUBSTR(PLGLDT,0,4) AS YEAR, COUNT(*) AS "RECORD COUNT" FROM BPCSF60/APL GROUP BY SUBSTR(PLGLDT,0,4) ORDER BY SUBSTR(PLGLDT,0,4) DESC. But I am getting error "Argument 2 of substringing function not valid". Can any one help me on this?

Software/Hardware used:
AS400

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 22  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • vibgyor
    I gave substr(plgldt,1,4) but it says... "Token ( was not valid."
    280 pointsBadges:
    report
  • vibgyor
    I tried substr(char(PLGLDT),1,4)... but it says.. "Token ( was not valid."
    280 pointsBadges:
    report
  • vibgyor
    The As400 version that i use is V4... I think SUBSTR will not be supported in this version.. is there any other alternative?
    280 pointsBadges:
    report
  • andyhumphreys
    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) ?
    465 pointsBadges:
    report
  • andyhumphreys
    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.
    465 pointsBadges:
    report
  • vibgyor
    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)
    280 pointsBadges:
    report
  • vibgyor
    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."
    280 pointsBadges:
    report
  • andyhumphreys
    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?
    465 pointsBadges:
    report
  • philpl1jb
    Odd, I just ran your  Jul 12, 2012  11:35 AM (GMT) statement. The command worked with my data on V5r4  
    51,355 pointsBadges:
    report
  • philpl1jb
    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
    51,355 pointsBadges:
    report
  • philpl1jb
    but not the editor .. you just can't put enough carrage returns into these discussions!!!
    51,355 pointsBadges:
    report
  • PutzGrilla
    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.                  
    795 pointsBadges:
    report
  • aceofdelts
    You can test the entire date via   > 20120000 and < 20129999
    1,985 pointsBadges:
    report
  • carlosdl
    "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 ?
    70,220 pointsBadges:
    report
  • philpl1jb
    "date field is in YYYYMMDD format."    Yes but what type is the field, numeric, char, date?
    51,355 pointsBadges:
    report
  • vibgyor
    the date field is a packed field..
    280 pointsBadges:
    report
  • philpl1jb
    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
    51,355 pointsBadges:
    report
  • andyhumphreys
    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?
    465 pointsBadges:
    report
  • TomLiotta
    ...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
    125,585 pointsBadges:
    report
  • philpl1jb
    Was there a problem with SUBSTR vs SUBSTRING in V4???
    51,355 pointsBadges:
    report
  • andyhumphreys
    @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..
    465 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following