Search code examples
sqloraclewhere-clausemaximo

Maximo search completed order yesterday SQL where clause

I'm trying to create a "dynamic" query to run a scheduled report every morning. I have a client that wish to have a report sent every morning where he gets information about closed tickets yesterday.

I have tried to search for yesterday with a query. see below:

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and upper(reportedby) = '[email protected]' and istask = 0 and (siteid = 'TPSELUN' or siteid = 'TPSELUO' and (actfinish between trunc(sysdate-1) and sysdate)))

I don't get an error message but the result is not correct. Is there another way of searching for actual finish from yesterday.

Solution

  • If you wish to find only yesterday's data then you need to try with

    actfinish between trunc(sysdate-1) and trunc(sysdate)- INTERVAL '1' SECOND

    or 

    trunc(actfinish) = trunc(sysdate-1)