SQLTeam.com | Weblogs | Forums

Question about having and where error with my code


#1
SELECT 
	A.DJ_NUMBER	as djNo
	,MAX(A.ACTION) as 'SetupStarted'
	,MIN(A.ACTION_TIME) AS 'SetupStart'
	,MAX(B.ACTION) as 'RunStarted'
	,MAX(B.ACTION_TIME) AS 'UpTimeStart'
	,MAX(C.ACTION) as 'RunEnded'
	,MAX(C.ACTION_TIME) AS 'UpTimeEnd'
	,FORMAT(DATEDIFF(SECOND, MAX(A.ACTION_TIME), MAX(B.ACTION_TIME))/60.0,'##.##') AS Setup
	,FORMAT(DATEDIFF(SECOND, MAX(B.ACTION_TIME), MAX(C.ACTION_TIME))/60.0, '##.##') AS UpTime	

FROM [AFL_MES2O_Interface_Data_arch] A, [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] B, [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] C
--WHERE (B.DJ_NUMBER = A.DJ_NUMBER AND MAX(B.ACTION_TIME) > Min(A.ACTION_TIME)) and (C.DJ_NUMBER = B.DJ_NUMBER AND MAX(C.ACTION_TIME) > MAX(B.ACTION_TIME))
GROUP BY A.DJ_NUMBER
HAVING (B.DJ_NUMBER = A.DJ_NUMBER AND MAX(B.ACTION_TIME) > Min(A.ACTION_TIME)) and (C.DJ_NUMBER = B.DJ_NUMBER AND MAX(C.ACTION_TIME) > MAX(B.ACTION_TIME))
ORDER BY SetupStart ASC, UpTimeEnd

Here is the error I am getting:

Msg 8121, Level 16, State 1, Line 15 Column 'StagingDB.dbo.AFL_MES2O_Interface_Data_arch.DJ_NUMBER' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. Msg 8121, Level 16, State 1, Line 15 Column 'StagingDB.dbo.AFL_MES2O_Interface_Data_arch.DJ_NUMBER' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. Msg 8121, Level 16, State 1, Line 15 Column 'StagingDB.dbo.AFL_MES2O_Interface_Data_arch.DJ_NUMBER' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.


#2

The "having" section id used for aggregation functions (min,max,sum ...). Also you're cartesian join except you don't join any fields (maybe you're trying to, in the "having" section). You should really consider using join. In you particular case:

  from afl_mes2O_interface_data_arch as a
       inner join stagingdb.dbo.afl_mes2O_interface_data_arch as b
               on b.dj_number=a.dj_number
       inner join stagingdb.dbo.afl_mes2O_interface_data_arch as c
               on c.dj_number=b.dj_number

and then your "having" section could lokke like this:

 having max(b.action_time)>min(a.action_time)
    and max(c.action_time)>max(b.action_time)

Also I find these lines strange. Seconds divided by 60 equals what?


#3

Thank you for your help.
Here is what I have done and it worked perfectly!
SELECT

					 A.DJ_NUMBER	AS JobNo
					,MAX(A.ACTION) as SetupStarted
					,MIN(A.ACTION_TIME) AS SetupStart	  
			--		,MAX(A.ACTION_TIME) AS 'SetupStartEnd'
					,MAX(B.ACTION) as RunStarted
			--		,MIN(B.ACTION_TIME) as 'RunStarted'	
					,MAX(B.ACTION_TIME) AS UpTimeStart
					,MAX(C.ACTION) as 'RunEnded'
			--		,MIN(C.ACTION_TIME) as 'UpTimeEndStart'
					,MAX(C.ACTION_TIME) AS UpTimeEnd 
					,FORMAT(DATEDIFF(SECOND, MIN(A.ACTION_TIME), MAX(B.ACTION_TIME))/60.0,'##.##') AS Setup
					,FORMAT(DATEDIFF(SECOND, MAX(B.ACTION_TIME), MAX(C.ACTION_TIME))/60.0,'##.##') AS UpTime	

			FROM
					[StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] as A
					INNER JOIN [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] as B
							ON (B.DJ_NUMBER = A.DJ_NUMBER  AND A.ACTION_TIME IS NOT NULL) AND B.ACTION_TIME > A.ACTION_TIME
					INNER JOIN [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] as C
							ON (C.DJ_NUMBER = B.DJ_NUMBER  AND B.ACTION_TIME IS NOT NULL) AND C.ACTION_TIME > B.ACTION_TIME 
			GROUP BY  A.DJ_NUMBER