Solved

Using Count within Subquery

Posted on 2002-07-12
7
336 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now