Solved

Using Count within Subquery

Posted on 2002-07-12
7
344 Views
Last Modified: 2012-05-05
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
Comment
Question by:jasonboetcher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7150628
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
 
LVL 1

Expert Comment

by:dmeili
ID: 7150631
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7150642
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:jasonboetcher
ID: 7154090
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 50 total points
ID: 7154251
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7161176
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
 

Expert Comment

by:CleanupPing
ID: 9280138
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question