Re: query with =ALL - Mailing list pgsql-sql
From | Jaime Casanova |
---|---|
Subject | Re: query with =ALL |
Date | |
Msg-id | BAY8-F51dXvTmtTruAV0007a81b@hotmail.com Whole thread Raw |
In response to | query with =ALL ("Jaime Casanova" <el_vigia_ec@hotmail.com>) |
Responses |
Re: query with =ALL
(Stephan Szabo <sszabo@megazone.bigpanda.com>)
|
List | 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 = 1AND 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 butif 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> thatrows 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'>> anyidea, 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