alessio Formation Lap

custom query postgres

in ServiceDesk Plus  •  Report  •  16 Jun, 06:12 AM
Hi,
I'm trying to extract the number of times than particular words apper in the Subject attribut, I wrote this query:
 

      SELECT to_char(to_timestamp(wo.createdtime/ 1000) AT TIME ZONE 'UTC-1', 'YYYY-MM-dd') as "Created date",count(*) as "Count"
   FROM workorder wo
   WHERE wo.title LIKE '%lent%' and wo.title like '%easy%'
  or wo.title LIKE '%blocc%' and wo.title like '%easy%'
  or wo.title LIKE '%rallent%' and wo.title like '%easy%'
   group by to_char(to_timestamp(wo.createdtime/ 1000) AT TIME ZONE 'UTC-1', 'YYYY-MM-dd')
   order by to_char(to_timestamp(wo.createdtime/ 1000) AT TIME ZONE 'UTC-1', 'YYYY-MM-dd')


Every time I execute the query in manage engine query editor I have no results but if I run the query in a different query manager directly connected to Postgres I have results!
Any tips?
Thanks

Replies (1)

Re: custom query postgres

19 Jun, 03:29 AM
Hi,

Please use the below query.

 SELECT to_char(to_timestamp(wo.createdtime/ 1000) AT TIME ZONE 'UTC-1', 'YYYY-MM-dd') as "Created date",count(wo.workorderid)  as "Count"
   FROM workorder wo
   WHERE (wo.title LIKE '%lent%' or wo.title like '%easy%'
  or wo.title LIKE '%blocc%' or wo.title like '%easy%'
  or wo.title LIKE '%rallent%' or wo.title like '%easy%')
   group by to_char(to_timestamp(wo.createdtime/ 1000) AT TIME ZONE 'UTC-1', 'YYYY-MM-dd') 
order by 1