From: Joe Conway <mail@joeconway.com>
To: Chris Faulkner <chrisf@oramap.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: length of array
Date: Thu, 28 Aug 2003 14:27:32 -0700
Message-ID: <3F4E73C4.3080407@joeconway.com> (raw)
In-Reply-To: <DGENKIKMJILAAKJGFHKFCENICGAA.chrisf@oramap.com>
References: <DGENKIKMJILAAKJGFHKFCENICGAA.chrisf@oramap.com>
Chris Faulkner wrote:
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, chrisf@oramap.com
Subject: Re: length of array
In-Reply-To: <3F4E73C4.3080407@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox