Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using Count within Subquery

Posted on 2002-07-12
7
Medium Priority
?
346 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

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 200 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

718 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