Re: query with =ALL

> 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