Select and Join Issue

[grabrail]

Hi can someone help.

I have the following code:

Set @LowTime ='01/02/2006 00:00'
Set @HighTime='01/02/2006 23:59'
set @counter ='1'


while @counter <= '28'
begin

select @LowTimeSeconds = DATEDIFF(second, @FixedStartDate, @LowTime)
select @HighTimeSeconds = DATEDIFF(second, @FixedStartDate, @HighTime)

insert into serverresults
SELECT @servername as Servername, t.name as Taskname, @lowtime , AVG(Dp.value)as AverageCounterName,MAX(Dp.value)as MaxCounterName from datapoint DP

JOIN taskbinder tb on dp.taskbinder_id = tb.taskbinder_id
JOIN server s on tb.server_id = s.server_id
JOIN counter c on tb.counter_id = c.counter_id
Join taskparam tp on tb.task_id = tp.task_id
Join task t on tp.taskdescr_id = t.taskdescr_id
where s.name = @ServerName
and c.name = @CounterName
and dp.recordtime between @LowTimeSeconds and @HighTimeSeconds

set @counter = @counter +1
set @lowtime = @lowtime +1
set @hightime = @lowtime +1
end

When I run it I get the following error:

Server: Msg 8118, Level 16, State 1, Line 59
Column 't.name' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.


If I put in the following command:

Group by taskname

I then get the follwoing error:

Server: Msg 207, Level 16, State 3, Line 59
Invalid column name 'taskname'.
Server: Msg 207, Level 16, State 1, Line 59
Invalid column name 'taskname'.

Can anybody help me get this working.

Cheers
J

 

[SQLDenis]

you have to group by t.name not by the alias

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ingernet]

Hi there,

Inherited some SQL madness from the guy who wrote this script. It's, sadly, broken, and the script is outside of my scope of knowledge - perhaps you can answer some questions for me?

He's basically got views querying views - like, 6 views deep. I've got this:

CREATE VIEW dbo.vTime_ElapsedByTask
AS
SELECT     [BEGIN].[id], [BEGIN].user_id, [BEGIN].[Date], [BEGIN].Task_Code, [BEGIN].Project_task, [BEGIN].task_paid, [BEGIN].Project_Number, 
                      [BEGIN].LoginTime AS START, [STOP] = CASE WHEN MIN([END].LoginTime) IS NULL THEN getdate() ELSE MIN([END].LoginTime) 
                      END, [TimeOnTask] = CASE WHEN MIN([END].LoginTime) IS NULL THEN CONVERT(decimal(9, 2), DATEDIFF(s, [BEGIN].LoginTime, getdate())) 
                      / 60 / 60 ELSE CONVERT(decimal(9, 2), DATEDIFF(s, [BEGIN].LoginTime, MIN([END].LoginTime))) / 60 / 60 END
FROM         dbo.vTime_Entries [END] RIGHT OUTER JOIN
                      dbo.vTime_Entries [BEGIN] ON [END].user_id = [BEGIN].user_id AND [END].LoginTime >= [BEGIN].LoginTime AND [BEGIN].[ID] <> [END].[ID]
GROUP BY [BEGIN].[id], [BEGIN].user_id, [BEGIN].[Date], [BEGIN].LoginTime, [BEGIN].Task_Code, [BEGIN].Project_task, [BEGIN].task_paid, 
                      [BEGIN].Project_Number, [BEGIN].[Date]

...I have to admit that I'm confused by all the BEGINs. I thought BEGIN was part of a control statement, but I don't see all that many ENDs. If you're wondering what <b>vTime_Entries</b> looks like:

CREATE VIEW dbo.vTime_Entries
AS
SELECT     dbo.vBaseTimeData.id, dbo.vBaseTimeData.user_id, CONVERT(datetime, CAST(DATEPART(month, dbo.vBaseTimeData.LoginTime) AS varchar(2)) 
                      + '/' + CAST(DATEPART(day, dbo.vBaseTimeData.LoginTime) AS varchar(2)) + '/' + CAST(DATEPART(year, dbo.vBaseTimeData.LoginTime) 
                      AS varchar(4))) AS [Date], CONVERT(int, RTRIM(dbo.vBaseTimeData.Task_Code)) AS Task_Code, dbo.vBaseTimeData.LoginTime, 
                      dbo.vActiveTaskCodes.task_description, dbo.vInterviewerList.user_Name, 
                      dbo.vInterviewerList.user_Fname + ' ' + dbo.vInterviewerList.user_Lname AS displayname, dbo.vBaseTimeData.Project_Number, 
                      dbo.vActiveTaskCodes.Project_task, dbo.vActiveTaskCodes.task_paid
FROM         dbo.vBaseTimeData INNER JOIN
                      dbo.vActiveTaskCodes ON CONVERT(int, dbo.vBaseTimeData.Task_Code) = CONVERT(int, dbo.vActiveTaskCodes.task_code) INNER JOIN
                      dbo.vInterviewerList ON dbo.vBaseTimeData.user_id = dbo.vInterviewerList.user_id

Again: I've just hit the stumbling block and can't figure out why he'd specify BEGIN so much. Not to mention sporadic use of square brackets around START and STOP.

Anyone got any ideas? I've been scratching my head over this for so long that I'm starting to chafe.

Thanks!
Inger

 

[gmmastros]

In this case, the Begin and Ends are table/view aliases. Since Begin and End are keywords, they must be surrounded by square brackets. In my opinion, using Begin and End as alias names is 'bad form' because they are keywords. Personally, I don't like objects that are keywords or have spaces. Of course, this is a personal preference thing, but I'm sure most people would agree with me.

I'd like to make 1 other point. When asking a completely un-related question, you are encouraged to start a new thread. From a forum organization perspective, it makes more sense.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom