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



  • 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.



  • 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... enter image description here

    whereas in the broken, you've picked up some commented out stuff first... enter image description here

    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. enter image description here

    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');
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2