Qlik Sense: PostgreSQL query - adding group by gives QVX_UNEXPECTED_END_OF_DATA
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.
The following error occurred:
QVX_UNEXPECTED_END_OF_DATA: Unable to get column information for the fields that are used in the query: Arithmetic operation resulted in an overflow.
Query that works in Qlik Sense:
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"
Query that gives error in Qlik Sense:
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"
Connection details:
CUSTOM CONNECT TO "provider=QvOdbcConnectorPackage.exe;driver=postgres;host=X;port=X;db=X;SSLMode=disable;UseSystemTrustStore=false;ByteaAsLongVarBinary=0;TextAsLongVarchar=0;UseUnicode=1;FetchTSWTZasTimestamp=1;MaxVarcharSize=262144;UseDeclareFetch=1;Fetch=200;EnableTableTypes=1;MoneyAsDecimal=1;QueryTimeout=3600;"
