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;

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