Code works fine till COALESCE

Question

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) 

Answer 1

You need to use GROUP BY to SUM.

SELECT COALESCE(SUM(Goals),0) AS TeamGoals
FROM Players
GROUP BY TeamId

Answer 2

After formatting your code so that we can see better where the various parts of the statement begins and ends, we can see what matches up with what.

Everything up to "till here it's fine" is one SQL SELECT statement. You need a semicolon to begin your next query, which starts with SELECT COALESCE(), but is incomplete since there is no FROM clause. Once you've put the terminator on the first statement it should run.

The second query is another question. You didn't show us the rest of the code. As TeKapa says, you need a GROUP BY clause anytime you use an aggregate function. But this is only required, if you are also including a non-aggregate column in the results.

SELECT TeamID, COALESCE(SUM(Goals),0) AS TeamGoals
  FROM Players
  GROUP BY TeamId

That will give you each TeamID in Players, and the total Goals for each team. You would probably also include ORDER BY TeamID

But if you simply want the combined total of all Players, it is completely valid to say

SELECT SUM(Goals) AS TotalGoals
  FROM Players

Taking a step back, it seems like your query has gotten so complex, that even you may be having difficulty managing it. Hopefully others wont be asked to maintain something like this.

If such code is going into production, I recommend finding ways to modularize portions of the complexity, such as with views, or common table expressions. It may also be a good idea to store those lists of values in a table, rather than hardcoding them.