I would like to do it like this in Oracle
select field from table N where [conditions] and NVL(N.level[1],0) = 0 and NVL(N.level[2],0) = 0 and NVL(N.level[3],0) = 0 and NVL(N.level[4],0) = 0
So if a row only has two elements in the array, but the first two both had values "0", then the row would return. At the moment, I have this :
and N.level[1] = 0 and N.level[2] = 0 and N.level[3] = 0 and N.level[4] = 0
but my row with 2 elements in the array won't be returned with this condition.
Is this what you're looking for?
regression=# select * from t1;
id | f1
----+-----------
1 | {1,2}
2 | {0,0,0}
3 | {0,0,0,0}
3 | {1,2,3,0}
(4 rows)regression=# SELECT * FROM t1 WHERE COALESCE(f1[1],0) = 0 and COALESCE(f1[2],0) = 0 and COALESCE(f1[3],0) = 0 and COALESCE(f1[4],0) = 0;
id | f1
----+-----------
2 | {0,0,0}
3 | {0,0,0,0}
(2 rows)
Joe
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
