Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    39

    Unanswered: Simple SQL Assistance needed - runtime variables

    Hello List,
    I am trying to run the following SQL on the AS400 (either as a standalone query or an stored proc, but I can't get it to work. Something similar works in MS SQL...can someone tell me what I need to do differently on the AS400?
    DECLARE CURDATE CHAR(6),
    DECLARE BEGDATE CHAR(6),

    SET CURDATE = SELECT MAX(SHUPMJ) FROM SYS7333.F3912
    SET BEGDATE = SELECT (MAX(SHUPMJ) - 7) FROM SYS7333.F9312

    SELECT * FROM SYS7333.F9312
    WHERE SHEVTYP = '01' AND SHEVSTAT = '02'
    AND (SHUPMJ BETWEEN begdate AND curdate)
    ORDER BY SHUPMJ, SHUPMT, SHUSER;

  2. #2
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    Re: Simple SQL Assistance needed - runtime variables

    Hmmh...

    I'm NOT an AS/400 expert, but why dont you try something like this:

    SELECT * FROM SYS7333.F9312
    WHERE SHEVTYP = '01' AND SHEVSTAT = '02'
    AND (SHUPMJ BETWEEN
    (SELECT (MAX(SHUPMJ) - 7) FROM SYS7333.F9312)
    AND
    (SELECT MAX(SHUPMJ) FROM SYS7333.F3912)
    )
    ORDER BY SHUPMJ, SHUPMT, SHUSER;

    Cheers, Bill

  3. #3
    Join Date
    Aug 2003
    Posts
    39

    Re: Simple SQL Assistance needed - runtime variables

    Originally posted by hurmavi
    Hmmh...

    I'm NOT an AS/400 expert, but why dont you try something like this:

    SELECT * FROM SYS7333.F9312
    WHERE SHEVTYP = '01' AND SHEVSTAT = '02'
    AND (SHUPMJ BETWEEN
    (SELECT (MAX(SHUPMJ) - 7) FROM SYS7333.F9312)
    AND
    (SELECT MAX(SHUPMJ) FROM SYS7333.F3912)
    )
    ORDER BY SHUPMJ, SHUPMT, SHUSER;

    Cheers, Bill
    Bill, I am having one of those moments where I feel like an idiot...

    I'm not sure why the subquery didn't occur to me.

    Thanks so much.

    Ryan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •