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

x
?
Solved

Help with Nested Derived Tables in a Store Procedure

Posted on 2006-06-16
5
Medium Priority
?
416 Views
Last Modified: 2010-07-27
Hi,

Anyone knows what's wrong with this code? I keep getting "Error 0: Syntax Error or access violation"
CREATE PROCEDURE rpt_NumberOfHits_UniqueIP_Detailed
(
    @StartDate varchar(20),
    @EndDate varchar(20),
    @SampleStartDate varchar(20),
    @SampleEndDate varchar(20)
)

AS

SELECT Count(IPAddress) AS HitCount FROM
(
      SELECT IPAddress FROM
      {
            SELECT IPAddress, Min(DateIn) AS MinDateIn
            FROM tbl_Hits
            WHERE (DateIn)>=CONVERT(DATETIME, @StartDate, 102) And (DateIn)<CONVERT(DATETIME, @EndDate, 102)
            GROUP BY IPAddress
      }  Step1
      WHERE (MinDateIn)>=CONVERT(DATETIME, @SampleStartDate, 102) And (MinDateIn)<CONVERT(DATETIME, @SampleEndDate, 102)
)  Step2
GO


I have tried nesting derived tables like this below code, and it's works fine:
CREATE PROCEDURE rpt_NumberOfHits_UniqueIP
(
    @StartDate varchar(20),
    @EndDate varchar(20)
)

AS

SELECT Count(IPAddress) AS HitCount FROM
(
SELECT IPAddress
FROM tbl_Hits
WHERE (((DateIn)>=CONVERT(DATETIME, @StartDate, 102) And (DateIn)<CONVERT(DATETIME, @EndDate, 102) ))
GROUP BY IPAddress
)  Step1
GO


But when I want to take it one step further, it bombs on me....


Yours Sincerely,
Jughead Yong.

0
Comment
Question by:jugheadyong
[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
5 Comments
 
LVL 5

Accepted Solution

by:
rmacfadyen earned 2000 total points
ID: 16925510
As a guess... you have a pair of Curly-Braces {} around one of the selects... shouldn't those be regular parenthesis ()?

Regards,

Rob
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16926043
Hi jugheadyong,

As rmacfadyen suggested replace those {}  with ()


Aneesh R!
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16926917
doesn't this do what you want?

CREATE PROCEDURE rpt_NumberOfHits_UniqueIP_Detailed
(
    @StartDate varchar(20),
    @EndDate varchar(20),
    @SampleStartDate varchar(20),
    @SampleEndDate varchar(20)
)

AS

declare @EndTest datetime
set @EndTest = convert(datetime,case when convert(datetime,@sampleenddate,102) < convert(datetime,@enddate,102) then @sampleenddate
                        else @enddate end)

          SELECT count(Distinct IPAddress) as Hitcount
          FROM tbl_Hits as H
          WHERE dateIn>=CONVERT(DATETIME, @SampleStartDate, 102)
            And DateIn<@endTest
            And Not exists (select ipaddress from tblhits as x  
                              where h.ipaddress = x.ipaddress
                                and datein >= convert(datetime,@startdate,102)
                                and datein < convert(datetime,@samplestartdate,102)


Return
GO

0
 

Author Comment

by:jugheadyong
ID: 16929146
You could just be right.

Let me go try it when I get back to office on Monday.

0
 

Author Comment

by:jugheadyong
ID: 16931424
You're absolutely right, rmacfadyen!

Ain't it funny how the solution turned out to be a silly little pair of brackets, eh?  I thought it would be something more complicated than that.  Points awarded to rmacfadyen.

Thanks to all who tried to helped.


Yours Sincerely,
Jonah.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

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