Hi there! I've got two essentially identical queri...
# suiteqla
Andrew
10/27/2022, 4:12 AMHi there! I've got two essentially identical queries but one of them is failing. Does anyone know why this might be? It seems like something odd going on with the TransactionLine.uniquekey field ...
Query that works:
Copy code
SELECT LISTAGG(TL.uniquekey, ',') line_ids, TL.transaction transaction_id
FROM TransactionLine TL
JOIN TransactionHistory ON TransactionHistory.transaction = TL.transaction
WHERE action = 'CHANGE'
AND datetime > TO_TIMESTAMP_TZ('2021-10-21 14:00:55 UTC', 'YYYY-MM-DD HH24:MI:SS TZR')
AND datetime <= TO_TIMESTAMP_TZ('2021-10-31 14:00:55 UTC', 'YYYY-MM-DD HH24:MI:SS TZR')
AND TL.subsidiary = '5'
GROUP BY TL.transaction; Copy code
SELECT LISTAGG(TL.uniquekey, ',') line_ids, TL.transaction transaction_id
FROM TransactionLine TL
JOIN TransactionHistory ON TransactionHistory.transaction = TL.transaction
WHERE action = 'CHANGE'
AND datetime > TO_TIMESTAMP_TZ('2021-10-31 14:00:55 UTC', 'YYYY-MM-DD HH24:MI:SS TZR')
AND datetime <= TO_TIMESTAMP_TZ('2021-11-10 14:00:55 UTC', 'YYYY-MM-DD HH24:MI:SS TZR')
AND TL.subsidiary = '5'
GROUP BY TL.transaction;c
Clay Roper
10/27/2022, 2:14 PMI don't know how sensitive the format strings are to nits like this, but you've got an extra space before
TZRTO_TIMESTAMP_TZa
Andrew
10/27/2022, 5:10 PMI appreciate the idea! This did not turn out to be the issue unfortunately
Andrew
10/27/2022, 5:11 PMI did manage to resolve this, though I couldn't explain why. With the 2nd query, I found that splitting up the time range fixed it. I saw no pattern to where to split it other than making it smaller by a few days fixed it.
Perplexing, but I'm at least able to continue
Andrew
10/27/2022, 8:09 PMI believe this is due to a limit in the number of character which can be passed to LISTAGG
3 Views