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

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