Combine SQL Server IIF function and the Count function in delphi-CodePudding

I develope an E-Mailing service and there is a form which must show all EMails related to a person in a grid.

I have the SQL query to display the result in a DBGrid like this:

SELECT
    FOLDERNAME,
    MAILFROM,
    MAILTO,
    LEFT(SUBJ, 200)   IIF(LEN(SUBJECT) > 200, '...', '')
        AS 'Subject',
    CAST(CNT AS VARCHAR(300))   IIF(LEN(CNT) > 300, '...', '')
        AS 'Content',
    STUFF(STUFF(STUFF(STUFF(MAILTIMESTAMP, 5, 0, '-'), 8, 0, '-'), 11, 0, ' '), 14, 0, ':')
        AS 'Date - Time',
    (SELECT COUNT(*) FROM ATTACHMENTS WHERE MESSAGEID = MESSAGES.MESSAGEID)
        AS Attachments
FROM
    MESSAGES, FOLDERS
WHERE
    FOLDERS.FOLDERID = MESSAGES.FOLDERID AND
    MESSAGES.PKEY = '4070486';

PKEY is the Person Key and Attachments is the number of attachments exists in database as BLOB.

The problem is that I cannot combine IFF function and Count function in the inner Select statement to display the Attachments like this:

SELECT IFF(COUNT(*) > 1, 'Yes', 'No')
FROM ATTACHMENTS WHERE MESSAGEID = MESSAGES.MESSAGEID

Note that every Email has one XML Attachment and if there are more than one attachments, it should write Yes otherwise No.

I have tried other things like WHERE EXISTS or IFF((SELECT...) > 1,...) and was not successfull.

I think there should be a better solution for that.

I'm using Delphi (TQuary, TDBGrid) and the database: SQL Server

CodePudding user response：

Have you tried replacing your IIF(...) with a CASE statement like this ?

SELECT
    CASE
        WHEN COUNT(*) <= 1 THEN 'No'
        ELSE 'Yes'
    END AS HAVE_MULTIPLE_VALUES
FROM ATTACHMENTS
WHERE MESSAGEID = MESSAGES.MESSAGEID

CodePudding user response：

SELECT
    FOLDERNAME,
    MAILFROM,
    MAILTO,
    LEFT(SUBJ, 200)   IIF(LEN(SUBJECT) > 200, '...', '')
        AS 'Subject',
    CAST(CNT AS VARCHAR(300))   IIF(LEN(CNT) > 300, '...', '')
        AS 'Content',
    STUFF(STUFF(STUFF(STUFF(MAILTIMESTAMP, 5, 0, '-'), 8, 0, '-'), 11, 0, ' '), 14, 0, ':')
        AS 'Date - Time',
    ATT.N AS Attachments
FROM
    MESSAGES, FOLDERS
LEFT JOIN (SELECT MESSAGEID, COUNT(*) as N FROM ATTACHMENTS GROUP BY MESSAGEID) ATT on ATT.MESSAGEID = MESSAGES.MESSAGEID    
WHERE
    FOLDERS.FOLDERID = MESSAGES.FOLDERID AND
    MESSAGES.PKEY = '4070486';