Solved

Help with Nested Derived Tables in a Store Procedure

Posted on 2006-06-16
5
388 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
5 Comments
 
LVL 5

Accepted Solution

by:
rmacfadyen earned 500 total points
Comment Utility
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
Comment Utility
Hi jugheadyong,

As rmacfadyen suggested replace those {}  with ()


Aneesh R!
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
Comment Utility
You could just be right.

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

0
 

Author Comment

by:jugheadyong
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Choosing SSD drives for SQL Server 32 77
C# SQL BULK INSERT CLASS 5 33
Access recordset not updateable 8 38
SQL JOIN 6 27
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…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

763 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

10 Experts available now in Live!

Get 1:1 Help Now