Re: query with =ALL - Mailing list pgsql-sql

> On Mon, 14 Jun 2004, Jaime Casanova wrote:>>  > On Mon, 14 Jun 2004, Jaime Casanova wrote:>  >>  >  > i have an
strangeresult 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_rubroRUB>  >  > 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
butif 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
subquerybut 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
theother 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