SQL error: aggregate function alias cannot be used in the WHERE clause

[Ben Hines]

I have a condition defined in my SQL connector configuration that selects the min and max timestamps from my data.  The goal is to produce a condition that has a single capsule that spans my entire data set.  The SQL query looks something like this:

 

SELECT 
	MIN(WO.RUNCOMPLETE) AS FirstSampleTime, 
	MAX(WO.RUNCOMPLETE) AS LastSampleTime 
FROM 
	...Joined tables...
WHERE 
	...Some conditions... 	

The resulting condition in Seeq workbench reports an error when compiling this:
 

SELECT 
	MIN(WO.RUNCOMPLETE) AS FirstSampleTime, 
	MAX(WO.RUNCOMPLETE) AS LastSampleTime 
FROM 
	...Joined tables...
WHERE 
	LASTSAMPLETIME IS NOT NULL 
	AND FIRSTSAMPLETIME IS NOT NULL 
	...Some conditions... 	
ORDER BY FIRSTSAMPLETIME DESC 
LIMIT 1

It seems Seeq is inserting WHERE conditions on LastSampleTime and FirstSampleTime and this is causing an error.  

Question 1:  Why is Seeq inserting the WHERE conditions? 
Question 2: Is there a workaround for this?  The data I'm using tends to be looked at in its entirety.  So, having a condition with a single capsule that spans the time for all samples is very handy.  

[Mike Cantrell]

HI,

I believe that you are running into the Seeq connector rule where the time columns must have a name.  There is a workaround by wrapping your query in another "Select *" query as referenced in the following section of our KB article.

[Ben Hines]

Thanks.  I solved this by putting my query inside a WITH like so: 
 

WITH Temp AS ( 
  SELECT 
      MIN(WO.RUNCOMPLETE) AS FirstSampleTime, 
      MAX(WO.RUNCOMPLETE) AS LastSampleTime 
  FROM 
      ...Joined tables...
  WHERE 
      ...Some conditions...
)
SELECT FirstSampleTime, LastSampleTime FROM Temp