From: "Jaime Casanova" <el_vigia_ec@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: query with =ALL
Date: Mon, 14 Jun 2004 19:07:53 +0000
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
Message-ID: <BAY8-F35GuluhfJpQPr0002c817@hotmail.com>

 > 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