Skip site navigation (1) Skip section navigation (2)

Re: How to build a query

From: Michael Wood <esiotrot(at)gmail(dot)com>
To: JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: How to build a query
Date: 2012-01-21 19:49:17
Message-ID: CAP6d-HWGU=L0BY3sNjbSnQp+q9EOiwgZV7LZLuy=iTFDs2emAA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 21 January 2012 01:20, JORGE MALDONADO <jorgemal1960(at)gmail(dot)com> wrote:
> I have to build a query at run-time which will contain a WHERE clause. There
> is a field in a table that has to be tested against several values but I do
> not know in advance how many values exist. For example,
>
> SELECT fld1, fld2, fld3 FROM tblTable
> WHERE fld1 = value[1] OR fld1 = value[2] OR fld1 = value[3]
>
> value[] is an array that contains all of the possible values that fld1 may
> take but I do not know how many items such array has. A value of -1 in the
> array position indicates that it will not be taken into account. So, I need
> to traverse the whole array and include those values different from -1 in
> the WHERE clause. I can do a FOR...NEXT and concatenate the conditions
> getting only those values different than -1 but I wonder if this is a good
> approach; the query might grow depending on the number of valid values in
> the array. Please advice if there is a better way to build my query.

Perhaps not what you want to hear, but if you had your values in a
separate table instead, you could do this:

SELECT fld1, fld2, fld3
FROM tblTable
INNER JOIN values ON tblTable.id = values.tblTable_id
WHERE values.value != -1;

But I suspect you know that.

-- 
Michael Wood <esiotrot(at)gmail(dot)com>

In response to

pgsql-novice by date

Next:From: Andy HalsallDate: 2012-01-25 12:41:59
Subject: generating deployment pkge from source
Previous:From: MickDate: 2012-01-21 00:54:27
Subject: Re: How to build a query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group