Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

Using Count within Subquery

I have the following query that I'm having problems with the subquery within "SELECT  EntDate       =".  Below is the full query:

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')
     IF 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)
                 ),
     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

Here are the errors I get:

Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near ')'.

0
jasonboetcher
Asked:
jasonboetcher
1 Solution
 
Anthony PerkinsCommented:
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
0
 
dmeiliCommented:
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')

0
 
Anthony PerkinsCommented:
Also, when you get a chance please maintain these open questions:
Changing from Date to DateTime datatype Date: 04/22/2002 10:37AM PST
http://www.experts-exchange.com/crystal/Q_20292323.html
Using SQL to retrieve data fro report Date: 03/22/2002 11:55AM PST
http://www.experts-exchange.com/crystal/Q_20280479.html
Difference between System and System32 directories? Date: 03/08/2002 06:51AM PST
http://www.experts-exchange.com/winnt/Q_20274921.html
Usage of the MAX Function Date: 05/28/2002 11:28AM PST
http://www.experts-exchange.com/mssql/Q_20305481.html
REPLACE function in SQL 6.5 Date: 05/14/2002 07:10AM PST
http://www.experts-exchange.com/mssql/Q_20300427.html
Error 208 - Invalid Object Name Date: 04/17/2002 07:06AM PST
http://www.experts-exchange.com/mssql/Q_20290493.html
Validating Social Security Number Date: 05/23/2002 10:17AM PST  
http://www.experts-exchange.com/powerbuilder/Q_20303990.html
Different Versions of ADO Date: 03/08/2002 11:12AM PST
http://www.experts-exchange.com/visualbasic/Q_20275036.html
Kill Background Process Date: 01/28/2002 12:11PM PST
http://www.experts-exchange.com/visualbasic/Q_20260399.html

Thanks,
Anthony
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jasonboetcherAuthor Commented:
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'.
0
 
Anthony PerkinsCommented:
CASE has this format:
CASE
    WHEN Boolean_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    ]
END

It appears you are missing the "THEN" keyword.

But aside, from this, I would suggest simplifying the statement until you get the syntax correct and then add all the pieces back again.

Anthony
0
 
Anthony PerkinsCommented:
I think you may have overlooked these questions:
Changing from Date to DateTime datatype Date: 04/22/2002 10:37AM PST  
http://www.experts-exchange.com/crystal/Q_20292323.html
Using SQL to retrieve data fro report Date: 03/22/2002 11:55AM PST
http://www.experts-exchange.com/crystal/Q_20280479.html
Error 208 - Invalid Object Name Date: 04/17/2002 07:06AM PST
http://www.experts-exchange.com/mssql/Q_20290493.html

Anthony






0
 
CleanupPingCommented:
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now