Using Count within Subquery

I suspect it is because you are using a If .. Else syntax in a SQL statement, when in fact a Case statement is called for.

Anthony

Looks like you may need another ')' at the end of:

    SELECT @count = convert(char, (SELECT count(*)
                          FROM IBSWIN.dbo.mleventlog mleventlog
                          WHERE refno = amend.refno AND
                          eventname = 'ISPL' AND
                          text = amend.refno + 'AMEND' + convert(char, amend.instance) + ' loaded via ISP')

Also, when you get a chance please maintain these open questions:
Changing from Date to DateTime datatype Date: 04/22/2002 10:37AM PST
https://www.experts-exchange.com/questions/20292323/Changing-from-Date-to-DateTime-datatype.html
Using SQL to retrieve data fro report Date: 03/22/2002 11:55AM PST
https://www.experts-exchange.com/questions/20280479/Using-SQL-to-retrieve-data-fro-report.html
Difference between System and System32 directories? Date: 03/08/2002 06:51AM PST
https://www.experts-exchange.com/questions/20274921/Difference-between-System-and-System32-directories.html
Usage of the MAX Function Date: 05/28/2002 11:28AM PST
https://www.experts-exchange.com/questions/20305481/Usage-of-the-MAX-Function.html
REPLACE function in SQL 6.5 Date: 05/14/2002 07:10AM PST
https://www.experts-exchange.com/questions/20300427/REPLACE-function-in-SQL-6-5.html
Error 208 - Invalid Object Name Date: 04/17/2002 07:06AM PST
https://www.experts-exchange.com/questions/20290493/Error-208-Invalid-Object-Name.html
Validating Social Security Number Date: 05/23/2002 10:17AM PST  
https://www.experts-exchange.com/questions/20303990/Validating-Social-Security-Number.html
Different Versions of ADO Date: 03/08/2002 11:12AM PST
https://www.experts-exchange.com/questions/20275036/Different-Versions-of-ADO.html
Kill Background Process Date: 01/28/2002 12:11PM PST
https://www.experts-exchange.com/questions/20260399/Kill-Background-Process.html

Thanks,
Anthony

I changed my query to the following:


DECLARE @dtBeginDate datetime
SELECT @dtBeginDate = '07/01/2002 12:00 AM'
DECLARE @dtEndDate datetime
SELECT @dtEndDate = '07/31/2002 11:59 PM'
DECLARE @count char

SELECT  EntDate       = (
     SELECT @count = convert(char, (SELECT count(*)
                           FROM IBSWIN.dbo.mleventlog mleventlog
                           WHERE refno = amend.refno AND
                           eventname = 'ISPL' AND
                           text = amend.refno + 'AMEND' + convert(char, amend.instance) + ' loaded via ISP'))
     CASE    
     WHEN convert(int, @count) > 0  
          SELECT convert(varchar(20), MIN(logtime), 111) + ' ' + convert(varchar(20), MIN(logtime), 114)
                FROM IBSWIN..mleventlog mleventlog
                WHERE refno = amend.refno AND
                eventname = 'ISPL' AND
          text = amend.refno + 'AMEND' + convert(char, amend.instance) + ' loaded via ISP'
     ELSE
          SELECT convert(varchar(20), MIN(logtime), 111) + ' ' + convert(varchar(20), MIN(logtime), 114)
                FROM IBSWIN..mleventlog mleventlog
                WHERE refno = amend.refno AND            
          eventname = 'AMDENTL' AND
          text = 'Entered Amendment ' + convert(char, amend.instance)
     END
                 ),
     IssDate            = convert(varchar(20), mleventlog.logtime, 111) + ' ' + convert(varchar(20), mleventlog.logtime, 114),
     RefNo          = amend.refno,
     AmendNo          = amend.instance,
     IssueBank     = customer.name,
     Currency     = amend.curr,
     Amount           = amend.amt,
     Operator     = usertable.user_name,
     UserID          = mleventlog.userid
FROM
     IBSWIN.dbo.lc lc INNER JOIN IBSWIN.dbo.folder folder ON lc.refno = folder.refno
     INNER JOIN IBSWIN.dbo.customer customer ON lc.issue_bk_mnem = customer.mnem
     INNER JOIN IBSWIN.dbo.amend amend ON lc.refno = amend.refno
     INNER JOIN IBSWIN.dbo.mleventlog mleventlog ON folder.refno = mleventlog.refno
     INNER JOIN IBSWIN.dbo.usertable usertable ON mleventlog.userid = usertable.user_id
WHERE
     amend.refno LIKE 'ELC%' AND
     (folder.state = -1 OR folder.state = 1) AND
     mleventlog.bus_dt >= @dtBeginDate AND
     mleventlog.bus_dt <= @dtEndDate AND
     mleventlog.eventname = 'AMDAPPL' AND
     mleventlog.text = 'Approved Amendment ' + convert(char, amend.instance)
ORDER BY
     UserID,
     RefNo,
     AmendNo


I now get the following errors:

Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'ELSE'.
Server: Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'END'.

I think you may have overlooked these questions:
Changing from Date to DateTime datatype Date: 04/22/2002 10:37AM PST  
https://www.experts-exchange.com/questions/20292323/Changing-from-Date-to-DateTime-datatype.html
Using SQL to retrieve data fro report Date: 03/22/2002 11:55AM PST
https://www.experts-exchange.com/questions/20280479/Using-SQL-to-retrieve-data-fro-report.html
Error 208 - Invalid Object Name Date: 04/17/2002 07:06AM PST
https://www.experts-exchange.com/questions/20290493/Error-208-Invalid-Object-Name.html

Anthony

jasonboetcher:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.