Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Can anyone suggest a reason for this?
I have a PostgreSQL query that runs fine in Qlik Sense, but if I add a group by statement, then I get an error in Qlik Sense. Both code snippets below run fine in PostgreSQL and in Qlikview.
SELECT ticket."street" as "idstreet", (del.id) as del FROM postoffice.ticket as ticket INNER JOIN postoffice.ticket_state_audit sa ON ticket.id=sa.id INNER JOIN postoffice.dels AS del ON del.id = (ticket.dest).hubcode LEFT JOIN postoffice.deliveries AS delivery ON ticket.id = delivery.ticketid AND delivery.pointtime IS NULL AND COALESCE(ticket.streetnumber,'') != '' WHERE (((delivery.id IS NOT NULL AND delivery.pointtime IS NULL) OR (delivery.id IS NULL AND ticket.pointtime IS NULL)) AND (ticket.dest).hubcode NOT IN ('AAA', 'BBB')) AND sa.updated >= NOW() - INTERVAL '1 DAY' -- group by ticket."street"
SELECT ticket."street" as "idstreet", max(del.id) as del FROM postoffice.ticket as ticket INNER JOIN postoffice.ticket_state_audit sa ON ticket.id=sa.id INNER JOIN postoffice.dels AS del ON del.id = (ticket.dest).hubcode LEFT JOIN postoffice.deliveries AS delivery ON ticket.id = delivery.ticketid AND delivery.pointtime IS NULL AND COALESCE(ticket.streetnumber,'') != '' WHERE (((delivery.id IS NOT NULL AND delivery.pointtime IS NULL) OR (delivery.id IS NULL AND ticket.pointtime IS NULL)) AND (ticket.dest).hubcode NOT IN ('AAA', 'BBB')) AND sa.updated >= NOW() - INTERVAL '1 DAY' group by ticket."street"
It's related to the data type bpchar on the database, in my case.
So I changed the data type to char, then the error "Arithmetic operation resulted in an overflow" was disappeared.