why does the order of fields return 0 rows, but move the field does

[guilherme]

I have two queries that return the same data, only the calculating field with the case is moved. I am using the Access Client Solutions "run query" to execute the query.

If I run this one

Select storenum,
       businessdate,
       kitchensenttime,
       parkcartime as delivertime,
       (parkcartime-kitchensenttime) as deliveryseconds,
       case
           when parkcartime-kitchensenttime>360 then 1
           else 0
       end as disastrouscount
from irislib.tblorder
where not(kitchensenttime='01/01/0001'
          or parkcartime='01/01/0001')
    and parkcartime>kitchensenttime
    and businessdate='05/18/2022'

No rows are returned.

While this one

Select storenum,
       businessdate,
       kitchensenttime,
       case
           when parkcartime-kitchensenttime>360 then 1
           else 0
       end as disastrouscount,
       parkcartime as delivertime,
       (parkcartime-kitchensenttime) as deliveryseconds
from irislib.tblorder
where not(kitchensenttime='01/01/0001'
          or parkcartime='01/01/0001')
    and parkcartime>kitchensenttime
    and businessdate='05/18/2022'

Will return rows. If I try the "broken" query with just the storenum and businessdate with just the disastrouscount calculation will it return rows.

[emmalee]

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 kitchensenttime and 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.
;
--try this
Select storenum,
       businessdate,
       kitchensenttime,
       parkcartime as delivertime,
       (parkcartime-kitchensenttime) as deliveryseconds,
       case
           when parkcartime-kitchensenttime>360 then 1
           else 0
       end as disastrouscount
from irislib.tblorder
where not(kitchensenttime = timestamp_iso(date('0001-01-01'))
          or parkcartime = timestamp_iso(date('0001-01-01'))
         )
    and parkcartime>kitchensenttime
    and businessdate= date('2022-05-18');