HI this code is working fine until the last statement there is more to it but was wondering if we can learn what is incorrect on this. this is on the ibm i (as400)
'SQL0199 Keyword Select Not Selected. Valid Tokens: For Use Skip Wait With Fetch Order Union Except Optimize' can you explain this issue to me?
SELECT COUNT(*)
FROM DLIB.ORDHEADR,DLIB.TRANCODE,DLIB.TRA11
WHERE OHCOM# = TSCOM# AND OHORD# = TSORD#
AND (otCOM# = OHCOM# AND OTORD#= OHORD# AND ottrnc = 'AQC')
AND OHORDT IN('RTR','INT','SAM')
AND OHREQD = replace(char(current date, iso), '-', '')
AND OHHLDC = ' '
AND ( ( TSTATS IN('AEP','SPJ')
AND OHORD# NOT in (SELECT a.TSORD#
FROM DLIB.TRANCODE a
WHERE a.TSTATS IN('EEP','SPC')
)
)
OR TSTATS IN('EEP','SPC')
AND OHORD# IN (SELECT DISTINCT(C.TSORD#)
FROM DLIB.TRANCODE C
JOIN (SELECT DISTINCT (B.TSORD#), MAX(B.TSUTIM) AS C_TSUTIM,
MAX(B.TSUDAT) AS C_TSUDAT
FROM DLIB.TRANCODE B
WHERE B.TSTATS IN ('EEP','SPC','ECM','ECT',
'ECA','CEL','BOC','COM',
'COO','REV','MCO','CPA',
'ECV','ECC','EPT','EPM',
'CAT','CAC','CAM','CAS',
'MAC','004','006','600',
'MEP','EPC','CPK')
GROUP BY B.TSORD#
) q1
ON C.TSORD# = q1.TSORD#
AND C.TSUDAT = q1.C_TSUDAT
AND C.TSUTIM = q1.C_TSUTIM
WHERE C.TSORD# NOT IN (SELECT F.TSORD#
FROM DLIB.TRANCODE F
WHERE F.TSTATS IN ('SPJ','REL','EAS','REV',
'STP','SPT','PPC','SPM',
'BPA','BPB','BPC','BPD','BPE',
'BPF','BPG','BPH','BPI','BPJ',
'BPK','BPL','BPM','BPN','CBM',
'BPO','BPP','BAT','BCM',
'BAM','WAT','WAM','LBL','012',
'006','600','004','SCP','CBA',
'CBB','CBC','CBD','CBE',
'CBF','CBG','CBH','CBI','CBJ',
'CBK','CBL','CBM','CBN','CBO',
'CBP','CBQ','CBR','CBS',
'CBT','CBU','CBV','CBW',
'CBX','CBY','CBZ','CB1',
'CB2','CB3','CB4','CB5')
)
AND C.TSTATS IN('EEP','SPC')
)
)
-- till here it's fine.
SELECT COALESCE(SUM(OdQty#),0)
You need to use GROUP BY to SUM.
SELECT COALESCE(SUM(Goals),0) AS TeamGoals
FROM Players
GROUP BY TeamId