N
N
Ness2021-12-28 17:41:18
SQL Server
Ness, 2021-12-28 17:41:18

How to fix the error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery?

Query from SQL Anywhere doesn't work on MS SQL, error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

SELECT (CASE
          WHEN EXISTS (SELECT 1 
                         FROM READCHK_READSEQ_OVRCOMP 
                         WHERE READCHK_ID = :readChkId AND READSEQ_ID = :readSeqId) 
              THEN 'M'
          WHEN ( COUNT(*) = COALESCE( SUM( SELECT COUNT(*) 
                                             FROM READCHKFLEX flx 
                                             WHERE flx.READCHKFLD_ID = rcfld.READCHKFLD_ID 
                                               AND flx.READCHK_ID =  :readChkId)), 0 ) 
              THEN 'A' 
          ELSE 'I' 
          END) AS CompletionStatus
  FROM READSEQFLD rsfld
    JOIN READSEQ rseq on rseq.READSEQ_ID = rsfld.READSEQ_ID
    JOIN READCHKPAGEFLD rcpf ON rcpf.READCHKTYPE_ID = rseq.READCHKTYPE_ID 
                               AND rcpf.READCHKFLD_ID = rsfld.READCHKFLD_ID
    JOIN READCHKFLD  rcfld ON rcfld.READCHKFLD_ID = rcpf.READCHKFLD_ID
  WHERE rsfld.READSEQ_ID = :readSeqId
    AND UPPER(rsfld.ACTIVATE_FLG) = 'Y'
    AND rcfld.CALFORM_ID IS NULL
    AND (rcpf.fld_properties is null OR UPPER(rcpf.fld_properties) <> 'HIDE')

Answer the question

In order to leave comments, you need to log in

1 answer(s)
K
Konstantin Tsvetkov, 2021-12-28
@Taifunov

MS SQL <> SYBASE (SQL Anywhere)
Replace COUNT(*)in the second WHENsubquery.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question