Solved

Help with Nested Derived Tables in a Store Procedure

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

623 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