Solved

Help with Nested Derived Tables in a Store Procedure

Posted on 2006-06-16
5
401 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
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL query to show nearest date 6 43
Need help in debugging a UDF results 7 29
Help With SQL Query 9 33
Stored Procedure needs owner to execute 5 24
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

821 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