Query to eliminate 0 from data

Ignition
1 
SELECT   T1HubOilFlowRate.t_stamp,
	T1HubOilFlowRate.T1__FlowRate
WHERE T1_FlowRate > 0
FROM T1HubOilFlowRate
WHERE t_stamp >= :StartDate
	AND t_stamp <= :EndDate
ORDER BY t_stamp

I'm trying to eliminate values of 0 from my data. I'm getting an error. Can anyone see what i'm missing?

2

You have two WHERE statements, combine it into a single WHERE after your FROM statement using AND and BETWEEN. Between is inclusive, so it accomplishes the same as
WHERE t_stamp >= :StartDate AND t_stamp <= :EndDate.

WHERE T1__FlowRate > 0 AND
t_stamp BETWEEN :StartDate AND :EndDate

Edit: Added missing underscore.

3

@ryan.white I did that but im getting this error now? Not sure how it could be an invalid column name.

image
image410×343 13.8 KB

SELECT   T1HubOilFlowRate.t_stamp,
	T1HubOilFlowRate.T1__FlowRate
FROM T1HubOilFlowRate
WHERE T1_FlowRate > 0 AND
t_stamp BETWEEN :StartDate AND :EndDate
ORDER BY t_stamp;
4

Ah, I quickly glanced at the column name and brain omitted the second _ in T1__FlowRate when making example code.

5

You have a single underscore in one column name, and a double underscore in the other.

Edit: Ryan fixed it faster that I could kibbitz.

2 Likes
6

@pturmel @ryan.white Good catch thank you

7

@pturmel @ryan.white Gathered some data since the change. I'm still getting some 0's in the dataset. The query seems good. Any idea where these can be coming from?

image
image510×505 18 KB

SELECT   T1HubOilFlowRate.t_stamp,
	T1HubOilFlowRate.T1_FlowRate
FROM T1HubOilFlowRate
WHERE T1_FlowRate > 0 AND
t_stamp BETWEEN :StartDate AND :EndDate
ORDER BY t_stamp;
8

Some extremely small value that is near zero and being rendered as a 0 because of rounding?

3 Likes
9

Are you sure these are not truncated values?
Maybe try WHERE T1_FlowRate > 0.01 AND

3 Likes
10

@David_Stone @ryan.white Let me try changing it to >.01. Thanks.