Hi I have been trying to create a view on ISeries DB2. The following view statement is working perfectly on db2 LUW and I exported this via db2move.

I get error Message: [SQL0104] Token <END-OF-STATEMENT> was not valid.

The detailed error is this. I know this is a syntax error but what about it ? I double checked spaces and the terminators. Is the terminator ";" needed for every subquery ?

Token <END-OF-STATEMENT> was not valid. Valid tokens: AS IN OUT <IDENTIFIER>.

Add "AS s" at end of the statement, like this...
where a.genfortreatysrno=b.genfortreatysrno ORDER BY a.GENFORTREATYSRNO,a.GENLAYERSSRNO) AS s;

(It was required older DB2 for LUW(perhaps, DB2 for LUW 9.1 or earlier), too.)

Are you using what version/release of DB2 for iSeries?
I guessed that you are using DB2 for iSeries V6R1.
But, if you are using DB2 for iSeries V5R4, it might not support ORDER bY in a sub-query.

Thanks for the reply Tonkuma ! Hope you are doing good. We are using V5 R4 I5 OS. The query is working from the tip you gave, previously I had to remove the order by to run it. Thanks once again !