I have slimmed down the query to remove potential complications, in addition I have verified that the fields are correct. DB2 UDB zSeries V7 is my db2 version.
SELECT STDINSTRCD, count(*) over(partition by STDINSTRCD), CAST(STDINSTRDESC AS VARCHAR(1000)) AS INSTR, C.STDINSTRSEQ, 1 FROM SYST.SCC004 C WHERE C.STDINSTRCD = '098'
I have tried a subqeury as well.
select H2.FRSTSTDINSTRCD, (select count(*) from SYST.scC004 Ci where '098'=Ci.STDINSTRCD) as cnt, cast(STDINSTRDESC as varchar(1000)), C.STDINSTRSEQ, 1 from SYST.scE4A00 H2 LEFT OUTER JOIN SYST.scC004 C ON C.STDINSTRCD = H2.FRSTSTDINSTRCD WHERE H2.CTLENTYID='MCS' AND H2.VCKVAL='12654' AND H2.POKVAL='0198617S12 000 000'
The error is receive is om.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: (;, FROM INTO sqlcode sqlstate
-104 Illegal Symbol token.
42601 A character, token, or clause is invalid or missing.
Any advice? I have been unable to determine what syntax error I might me making.