I'm with Mustaccio, there's no reason moving the calculated field should cause the rows to change.
I did notice that the statement being run in the working screenshot appears to be just the statement you're expecting...
whereas in the broken, you've picked up some commented out stuff first...
I also notice you've not terminated any statements, with a
;. I'd suggest you do so. As the only explanation I can come up with is that you're picking up some code you don't intended to. On the
Messages tab of Run SQL Scripts, take a look at the statement actually being run.
In addition, you should always use ISO date/time format for literals.
It appears that
parkcartime are timestamps, not dates. Comparing them to a date forces the DB to implicitly convert either the column value or the literal. It's better practice to explicitly do the conversions yourself.
Furthermore, '0001-01-01' is suspect, if the column is nullable, it's more likely that the columns are null and don't actually contain '0001-01-01-00:00:00.000000'. If the columns are defined NOT NULL and the records are being written out using RPG op-codes, then RPG is defaulting in the '0001-01-01-00:00:00.000000'
--having this semicolon will ensure the parser doesn't see
-- any prior statements as being a part of this one.
parkcartime as delivertime,
(parkcartime-kitchensenttime) as deliveryseconds,
when parkcartime-kitchensenttime>360 then 1
end as disastrouscount
where not(kitchensenttime = timestamp_iso(date('0001-01-01'))
or parkcartime = timestamp_iso(date('0001-01-01'))
and businessdate= date('2022-05-18');