Answer the question
In order to leave comments, you need to log in
How to check an array that all values are IS NULL?
In the process of solving the issue: display NULL if all values in the (result) array IS NULL (instead of an array with NULLs). The result is the following expression:
SELECT ...
CASE WHEN ('A' = ALL(ARRAY_AGG(t1.value)) IS NULL) THEN null ELSE ARRAY_AGG(t1.value)
'A' =
... If we check an array with numbers - you can write 1 =
or 999 =
- it works the same way ... actually, I also wrote "A" from the bulldozer and it works. Answer the question
In order to leave comments, you need to log in
I don’t know how you did the join on your last question, but you had to first select unique records from the first table, and then join the second table to it and aggregate value into an array, then where in the second table there is nothing to the first in the array, just one null element, respectively, we take the first element of the array and look at it for null, etc.
in general something like this:
SELECT
t.a1, t.a2, t.a3,
CASE WHEN ( ARRAY_AGG(t2.value) )[1] IS null THEN null ELSE ARRAY_AGG(t2.value) END AS arr
FROM (
SELECT
t1.a1,
t1.a2,
t1.a3
FROM table1 t1
GROUP BY t1.a1, t1.a2, t1.a3
) t
LEFT JOIN table2 t2 ON t.a1 = t2.b1 AND t.a2 = t2.b2 AND t.a3 = t2.b3
GROUP BY t.a1, t.a2, t.a3
SELECT
t.a1, t.a2, t.a3,
CASE WHEN
(ARRAY_DIMS(ARRAY_AGG(t2.value)) = '[1:1]')
AND ( ARRAY_AGG(t2.value) )[1] IS null
THEN null ELSE ARRAY_AGG(t2.value)
END AS arr
FROM (
SELECT
t1.a1,
t1.a2,
t1.a3
FROM table1 t1
GROUP BY t1.a1, t1.a2, t1.a3
) t
LEFT JOIN table2 t2 ON t.a1 = t2.b1 AND t.a2 = t2.b2 AND t.a3 = t2.b3
GROUP BY t.a1, t.a2, t.a3
CASE WHEN
(ARRAY_DIMS(ARRAY_AGG(t2.value)) = '[1:1]')
AND ( ARRAY_AGG(t2.value) )[1] IS null
THEN null ELSE ARRAY_AGG(t2.value)
END
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question