pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquery with =ALL
12+ messages / 4 participants
[nested] [flat]
* query with =ALL
@ 2004-06-14 16:49 Jaime Casanova <el_vigia_ec@hotmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Jaime Casanova @ 2004-06-14 16:49 UTC (permalink / raw)
To: pgsql-sql
Hi all,
i have an strange result here, i'm using 7.4.2 on redhat 8
i have a query like this
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND
CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND
CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND
CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso WHERE
ent_codigo = 1 AND
sec_codigo = 1 AND
ani_codigo = '2004-2005' AND
cic_codigo = 1 AND
esp_codigo = 0 AND
cur_codigo = 1)
that bring no values but if i do this (the same query without the =ALL part)
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND
CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND
CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1
get a resultset, one would think that the =ALL subquery is filtering out
that rows but the cur_paralelo has same values in both sides
select distinct cur_paralelo from rec_m_cuadropagos WHERE ent_codigo = 1 AND
sec_codigo = 1 AND
ani_codigo = '2004-2005' AND
cic_codigo = 1 AND
esp_codigo = 0 AND
cur_codigo = 1
intersect
SELECT cur_paralelo FROM aca_t_curso WHERE ent_codigo = 1 AND
sec_codigo = 1 AND
ani_codigo = '2004-2005' AND
cic_codigo = 1 AND
esp_codigo = 0 AND
cur_codigo = 1
this query proves that its result is 'A', 'B', 'C'
any idea, is something wrong in my thinking?
thanx in advance,
Jaime Casanova
_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query with =ALL
@ 2004-06-14 17:38 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Jaime Casanova <el_vigia_ec@hotmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Stephan Szabo @ 2004-06-14 17:38 UTC (permalink / raw)
To: Jaime Casanova <el_vigia_ec@hotmail.com>; +Cc: pgsql-sql
On Mon, 14 Jun 2004, Jaime Casanova wrote:
> i have an strange result here, i'm using 7.4.2 on redhat 8
>
> i have a query like this
>
> SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
> CPA.cpa_fechavencimiento
> FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
> WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
> AND
> CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
> '2004-2005' AND
> CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND
> CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso WHERE
> ent_codigo = 1 AND
>
> sec_codigo = 1 AND
>
> ani_codigo = '2004-2005' AND
>
> cic_codigo = 1 AND
>
> esp_codigo = 0 AND
>
> cur_codigo = 1)
>
> that bring no values but if i do this (the same query without the =ALL part)
>
> SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
> CPA.cpa_fechavencimiento
> FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
> WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
> AND
> CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
> '2004-2005' AND
> CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1
>
> get a resultset, one would think that the =ALL subquery is filtering out
> that rows but the cur_paralelo has same values in both sides
>
> select distinct cur_paralelo from rec_m_cuadropagos WHERE ent_codigo = 1 AND
>
> sec_codigo = 1 AND
>
> ani_codigo = '2004-2005' AND
>
> cic_codigo = 1 AND
>
> esp_codigo = 0 AND
>
> cur_codigo = 1
> intersect
> SELECT cur_paralelo FROM aca_t_curso WHERE ent_codigo = 1 AND
>
> sec_codigo = 1 AND
>
> ani_codigo = '2004-2005' AND
>
> cic_codigo = 1 AND
>
> esp_codigo = 0 AND
>
> cur_codigo = 1
>
> this query proves that its result is 'A', 'B', 'C'
>
> any idea, is something wrong in my thinking?
AFAIK, The query isn't check all values that meet the other criteria
against all values of the subquery but instead check the value from each
row that meets the other criteria against all the values in the subquery.
If the values in the subquery are distinct, it's not going to return true.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query with =ALL
@ 2004-06-14 17:48 Richard Huxton <dev@archonet.com>
parent: Jaime Casanova <el_vigia_ec@hotmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Richard Huxton @ 2004-06-14 17:48 UTC (permalink / raw)
To: Jaime Casanova <el_vigia_ec@hotmail.com>; +Cc: pgsql-sql
Jaime Casanova wrote:
> Hi all,
>
> i have an strange result here, i'm using 7.4.2 on redhat 8
>
> i have a query like this
[snip]
> this query proves that its result is 'A', 'B', 'C'
> any idea, is something wrong in my thinking?
Do you have any null values involved? That might well interfere (though
I admit I haven't looked in detail)
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query with =ALL
@ 2004-06-14 18:01 Jaime Casanova <el_vigia_ec@hotmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Jaime Casanova @ 2004-06-14 18:01 UTC (permalink / raw)
To: pgsql-sql
>Jaime Casanova wrote:
>>Hi all,
>>
>>i have an strange result here, i'm using 7.4.2 on redhat 8
>>
>>i have a query like this
>
>[snip]
>
>>this query proves that its result is 'A', 'B', 'C'
>>any idea, is something wrong in my thinking?
>Do you have any null values involved? That might well interfere (though I
>admit I haven't looked in >detail)
There are no null values here, i'm sure of that because all the fields in
the subquery included the one i'm returning are part of the PK
>--
> Richard Huxton
> Archonet Ltd
and when i do the selects individually i got the correct results
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND
CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND
CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND
CPA.cur_paralelo = 'A '
1 Matrícula
100 05/10/2005
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND
CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND
CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND
CPA.cur_paralelo = 'B '
1 Matrícula
100 05/10/2005
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND
CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND
CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND
CPA.cur_paralelo = 'C '
1 Matrícula
100 05/10/2005
_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query with =ALL
@ 2004-06-14 18:06 Jaime Casanova <el_vigia_ec@hotmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Jaime Casanova @ 2004-06-14 18:06 UTC (permalink / raw)
To: pgsql-sql
On Mon, 14 Jun 2004, Jaime Casanova wrote:
> i have an strange result here, i'm using 7.4.2 on redhat 8
>
> i have a query like this
>
> SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
> CPA.cpa_fechavencimiento
> FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
> WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
> AND
> CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
> '2004-2005' AND
> CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1
AND
> CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso WHERE
> ent_codigo = 1 AND
>
> sec_codigo = 1 AND
>
> ani_codigo = '2004-2005' AND
>
> cic_codigo = 1 AND
>
> esp_codigo = 0 AND
>
> cur_codigo = 1)
>
> that bring no values but if i do this (the same query without the =ALL
part)
>
> SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
> CPA.cpa_fechavencimiento
> FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
> WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
> AND
> CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
> '2004-2005' AND
> CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1
>
> get a resultset, one would think that the =ALL subquery is filtering out
> that rows but the cur_paralelo has same values in both sides
>
> select distinct cur_paralelo from rec_m_cuadropagos WHERE ent_codigo = 1
AND
>
> sec_codigo = 1 AND
>
> ani_codigo = '2004-2005' AND
>
> cic_codigo = 1 AND
>
> esp_codigo = 0 AND
>
> cur_codigo = 1
> intersect
> SELECT cur_paralelo FROM aca_t_curso WHERE ent_codigo = 1 AND
>
> sec_codigo = 1 AND
>
> ani_codigo = '2004-2005' AND
>
> cic_codigo = 1 AND
>
> esp_codigo = 0 AND
>
> cur_codigo = 1
>
> this query proves that its result is 'A', 'B', 'C'
>
> any idea, is something wrong in my thinking?
AFAIK, The query isn't check all values that meet the other criteria
against all values of the subquery but instead check the value from each
row that meets the other criteria against all the values in the subquery.
If the values in the subquery are distinct, it's not going to return true.
Is there another solution instead the one i'm using can you help me in that?
_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query with =ALL
@ 2004-06-14 18:26 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Jaime Casanova <el_vigia_ec@hotmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Stephan Szabo @ 2004-06-14 18:26 UTC (permalink / raw)
To: Jaime Casanova <el_vigia_ec@hotmail.com>; +Cc: pgsql-sql
On Mon, 14 Jun 2004, Jaime Casanova wrote:
> On Mon, 14 Jun 2004, Jaime Casanova wrote:
>
> > i have an strange result here, i'm using 7.4.2 on redhat 8
> >
> > i have a query like this
> >
> > SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
> > CPA.cpa_fechavencimiento
> > FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
> > WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
> > AND
> > CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
> > '2004-2005' AND
> > CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1
> AND
> > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso WHERE
> > ent_codigo = 1 AND
> >
> > sec_codigo = 1 AND
> >
> > ani_codigo = '2004-2005' AND
> >
> > cic_codigo = 1 AND
> >
> > esp_codigo = 0 AND
> >
> > cur_codigo = 1)
> >
> > that bring no values but if i do this (the same query without the =ALL
> part)
> AFAIK, The query isn't check all values that meet the other criteria
> against all values of the subquery but instead check the value from each
> row that meets the other criteria against all the values in the subquery.
> If the values in the subquery are distinct, it's not going to return true.
> Is there another solution instead the one i'm using can you help me in that?
What precisely are you looking for as your output? Only those
combinations of the four output attributes that meet the other criteria
and that have exactly the same set as in the subselect? Hmm, I'm not sure
how to do that off hand, will think about it, but hopefully someone will
have an answer.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query with =ALL
@ 2004-06-14 19:07 Jaime Casanova <el_vigia_ec@hotmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Jaime Casanova @ 2004-06-14 19:07 UTC (permalink / raw)
To: pgsql-sql
> On Mon, 14 Jun 2004, Jaime Casanova wrote:
>
> > On Mon, 14 Jun 2004, Jaime Casanova wrote:
> >
> > > i have an strange result here, i'm using 7.4.2 on redhat 8
> > >
> > > i have a query like this
> > >
> > > SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
> > > CPA.cpa_fechavencimiento
> > > FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
> > > WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo =
CPA.rub_codigo
> > > AND
> > > CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo
=
> > > '2004-2005' AND
> > > CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo
= 1
> > AND
> > > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso
WHERE
> > > ent_codigo = 1 AND
> > >
> > > sec_codigo = 1 AND
> > >
> > > ani_codigo = '2004-2005' AND
> > >
> > > cic_codigo = 1 AND
> > >
> > > esp_codigo = 0 AND
> > >
> > > cur_codigo = 1)
> > >
> > > that bring no values but if i do this (the same query without the
=ALL
> > part)
> > AFAIK, The query isn't check all values that meet the other criteria
> > against all values of the subquery but instead check the value from
each
> > row that meets the other criteria against all the values in the
subquery.
> > If the values in the subquery are distinct, it's not going to return
true.
> > Is there another solution instead the one i'm using can you help me in
that?
> What precisely are you looking for as your output? Only those
> combinations of the four output attributes that meet the other criteria
> and that have exactly the same set as in the subselect? Hmm, I'm not
sure
> how to do that off hand, will think about it, but hopefully someone will
> have an answer.
hi all,
What i'm trying to do is to obtain the same result that i should obtain if i
do:
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND
CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND
CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND
CPA.cur_paralelo = 'A '
intersect
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND
CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND
CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND
CPA.cur_paralelo = 'B '
intersect
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND
CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND
CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND
CPA.cur_paralelo = 'C '
but i cannot use the intersect solution becuase i don't know how many values
of cur_paralelo could be involved becuase that depends on user input.
maybe someone can help.
thanx in advance,
Jaime Casanova
_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query with =ALL
@ 2004-06-14 19:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jaime Casanova <el_vigia_ec@hotmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2004-06-14 19:41 UTC (permalink / raw)
To: Jaime Casanova <el_vigia_ec@hotmail.com>; +Cc: pgsql-sql
"Jaime Casanova" <el_vigia_ec@hotmail.com> writes:
> AND
> CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso ...)
Wait a second ... we are all overthinking the problem. The subselect
returns three *different* values. It is not possible for any
CPA.cur_paralelo values to be simultaneously equal to all three.
So this test certainly fails at every row of CPA.
Perhaps you meant "= ANY"?
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query with =ALL
@ 2004-06-14 19:53 Jaime Casanova <el_vigia_ec@hotmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Jaime Casanova @ 2004-06-14 19:53 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-sql
Hi all,
> > AND
> > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso ...)
>
>Wait a second ... we are all overthinking the problem. The subselect
>returns three *different* values. It is not possible for any
>CPA.cur_paralelo values to be simultaneously equal to all three.
>So this test certainly fails at every row of CPA.
>
>Perhaps you meant "= ANY"?
Not really becuase ANY has the same efect that IN and what i want is all the
results that are equal in all the rows in any of the cur_paralelo values.
But must be in all the cur_paralelo or nothing.
thanx in advance,
Jaime Casanova
_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query with =ALL
@ 2004-06-14 19:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jaime Casanova <el_vigia_ec@hotmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2004-06-14 19:55 UTC (permalink / raw)
To: Jaime Casanova <el_vigia_ec@hotmail.com>; +Cc: pgsql-sql
"Jaime Casanova" <el_vigia_ec@hotmail.com> writes:
>> Perhaps you meant "= ANY"?
> Not really becuase ANY has the same efect that IN and what i want is all the
> results that are equal in all the rows in any of the cur_paralelo values.
> But must be in all the cur_paralelo or nothing.
You're not expressing yourself clearly, because as far as I can
understand you there are guaranteed to be no such results.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query with =ALL
@ 2004-06-14 20:04 Jaime Casanova <el_vigia_ec@hotmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Jaime Casanova @ 2004-06-14 20:04 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-sql
>You're not expressing yourself clearly, because as far as I can
>understand you there are guaranteed to be no such results.
>
> regards, tom lane
ok,
the output i want is equivalent to:
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND
CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND
CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND
CPA.cur_paralelo = 'A '
intersect
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND
CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND
CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND
CPA.cur_paralelo = 'B '
intersect
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
AND
CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND
CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND
CPA.cur_paralelo = 'C '
but i cannot use that solution because the columns in wich i want to so such
type of query are various and depends on user input
_________________________________________________________________
Help STOP SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query with =ALL
@ 2004-06-14 20:07 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Stephan Szabo @ 2004-06-14 20:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jaime Casanova <el_vigia_ec@hotmail.com>; pgsql-sql
On Mon, 14 Jun 2004, Tom Lane wrote:
> "Jaime Casanova" <el_vigia_ec@hotmail.com> writes:
> > AND
> > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso ...)
>
> Wait a second ... we are all overthinking the problem. The subselect
> returns three *different* values. It is not possible for any
> CPA.cur_paralelo values to be simultaneously equal to all three.
> So this test certainly fails at every row of CPA.
>
> Perhaps you meant "= ANY"?
I think what he wants is to output distinct sets of output columns where
for each of these sets of output columns there exists a set of rows that
meet the other conditions and that the set of cur_paralelo values for
that set of rows is the same as (or perhaps is a superset of) the set of
values returned from the subselect.
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2004-06-14 20:07 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-14 16:49 query with =ALL Jaime Casanova <el_vigia_ec@hotmail.com>
2004-06-14 17:38 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-14 17:48 ` Richard Huxton <dev@archonet.com>
2004-06-14 18:01 Re: query with =ALL Jaime Casanova <el_vigia_ec@hotmail.com>
2004-06-14 18:06 Re: query with =ALL Jaime Casanova <el_vigia_ec@hotmail.com>
2004-06-14 18:26 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-14 19:07 Re: query with =ALL Jaime Casanova <el_vigia_ec@hotmail.com>
2004-06-14 19:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-14 20:07 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-14 19:53 Re: query with =ALL Jaime Casanova <el_vigia_ec@hotmail.com>
2004-06-14 19:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-14 20:04 Re: query with =ALL Jaime Casanova <el_vigia_ec@hotmail.com>
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