|
|
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)
HXTT DBF
|
problem with subquerys |
Carlos Alva |
2006-05-23 15:45:02 |
I have a new problem when I used a subquery
the next query:
select p.per_cve
from r_perso as p
where at('CER',p.per_gpos) != 0
and p.per_habili = '9'
and at('4',p.per_zonas) != 0
and p.per_hrde <= '13:00'
and p.per_hra >= '13:00'
and at(p.per_mnu,p.per_mnu) != 0
and not ((not (at('N',p.per_mnu) = 0 and at(left('P01',3),p.per_mnu)!= 0)
or (at('N',p.per_mnu) != 0 and at(left('P01',3),p.per_mnu)= 0))
and (left('P01',1) = '9'))
returns this result set:
per_cve
-------
MRM
VZR
SRE
FLL
ARO
EDV
AGB
when I used like subquery I obtained "single row subquery return no rows"
the new query is this:
select r_perso.per_cve, r_perso.per_punmax, r_perso.per_nom
from r_perso
where (r_perso.per_cve) = (select top 1 p.per_cve
from r_perso as p
where at('CER',p.per_gpos) != 0
and p.per_habili = '9'
and at('4',p.per_zonas) != 0
and p.per_hrde <= '13:00'
and p.per_hra >= '13:00'
and at(p.per_mnu,p.per_mnu) != 0
and not ((not (at('N',p.per_mnu) = 0 and at(left('P01',3),p.per_mnu)!= 0)
or (at('N',p.per_mnu) != 0 and at(left('P01',3),p.per_mnu)= 0))
and (left('P01',1) = '9')))
and when I used with in, I obtained a null pointer mismatch
select r_perso.per_cve, r_perso.per_punmax, r_perso.per_nom
from r_perso
where (r_perso.per_cve) in (select p.per_cve
from r_perso as p
where at('CER',p.per_gpos) != 0
and p.per_habili = '9'
and at('4',p.per_zonas) != 0
and p.per_hrde <= '13:00'
and p.per_hra >= '13:00'
and at(p.per_mnu,p.per_mnu) != 0
and not ((not (at('N',p.per_mnu) = 0 and at(left('P01',3),p.per_mnu)!= 0)
or (at('N',p.per_mnu) != 0 and at(left('P01',3),p.per_mnu)= 0))
and (left('P01',1) = '9')))
I don't understan why it happens, I wait for you help, thanks.
|
Re:problem with subquerys |
HXTT Support |
2006-05-23 21:41:34 |
>when I used like subquery I obtained "single row subquery return no rows"
>the new query is this:
No recurred. It should work normal.
>and when I used with in, I obtained a null pointer mismatch
Recurred. It's resulted by bitmap index on a null value. Supported.
Please download the latest package.
BTW, why are you using at(p.per_mnu,p.per_mnu) != 0 ?
|
Re:Re:problem with subquerys |
Carlos Alva |
2006-05-23 23:45:51 |
BTW, why are you using at(p.per_mnu,p.per_mnu) != 0 ?
I'm sorry, I forget to say that this query works with some variables, in some cases the variable recives the value of the field so that it allows to obtain results, I used this comparison to show the query in its original form.
ok, the subquery works with the new version, nevertheless I found another problem, when I executed this query:
select p.per_cve+'20050930' clave
from r_perso p
where not(at('CER',p.per_gpos) = 0)
and p.per_habili = '9'
and not(at('4',p.per_zonas) = 0)
and '14:00' BETWEEN p.per_hrde and p.per_hra
and not(at(LEFT('903',1),p.per_mnu) = 0)
I obtain in less than 1 second the following result:
clave
MRM20050930
VZF20050930
SRE20050930
FLL20050930
ARO20050930
EDV20050930
AGB20050930
when I used one of this records and execute the next query, I obtain this result in less than 1 second
select c.ccperrev pers, sum(a.acpuntos) puntos
from r_actos a, r_calcab c
where c.ccperrev+dtos(c.ccfecrev) = 'MRM20050930'
and a.acgruact+a.acacto = alltrim(c.ccacto)
and c.ccfechamax <> c.ccfeclis
group by c.ccperrev
pers puntos
---- ------
MRM 89
But, when I used the first query as subquery, the result is ready in most of 2 minutes
select cal.ccperrev, sum(act.acpuntos)
from (select p.per_cve+'20050930' clave
from r_perso p
where not(at('CER',p.per_gpos) = 0)
and p.per_habili = '9'
and not(at('4',p.per_zonas) = 0)
and '14:00' BETWEEN p.per_hrde and p.per_hra
and not(at(LEFT('903',1),p.per_mnu) = 0)) per, r_actos act, r_calcab cal
where cal.ccperrev+dtos(cal.ccfecrev) = per.clave
and act.acgruact+act.acacto = alltrim(cal.ccacto)
and not(cal.ccfechamax = cal.ccfeclis)
group by cal.ccperrev
I also tried with the next query
select cal.ccperrev, sum(act.acpuntos)
from r_perso per, r_actos act, r_calcab cal
where per.per_cve in
(select p.per_cve
from r_perso p
where not(at('CER',p.per_gpos) = 0)
and p.per_habili = '9'
and not(at('4',p.per_zonas) = 0)
and '14:00' BETWEEN p.per_hrde and p.per_hra
and not(at(LEFT('903',1),p.per_mnu) = 0))
and c.ccperrev+dtos(c.ccfecrev) = p.per_cve+'20050930'
and a.acgruact+a.acacto = alltrim(c.ccacto)
and c.ccfechamax <> c.ccfeclis
group by cal.ccperrev
How can I execute the union of both single querys and obtain a better response time?
thanks again
|
Re:Re:Re:problem with subquerys |
HXTT Support |
2006-05-24 00:46:13 |
Try:
select cal.ccperrev, sum(act.acpuntos)
from (select p.per_cve+'20050930' clave
from r_perso p
where (at('CER',p.per_gpos) >0)
and p.per_habili = '9'
and (at('4',p.per_zonas) > 0)
and p.per_hrde <='14:00' and p.per_hra>='14:00'
and (at(LEFT('903',1),p.per_mnu) >0)) per, r_actos act, r_calcab cal
where cal.ccperrev+dtos(cal.ccfecrev) = per.clave
and act.acgruact+act.acacto = alltrim(cal.ccacto)
and (cal.ccfechamax != cal.ccfeclis)
group by cal.ccperrev
Or
select cal.ccperrev, sum(act.acpuntos)
from r_perso per, r_actos act, r_calcab cal
where per.per_cve in
(select p.per_cve
from r_perso p
where (at('CER',p.per_gpos) > 0)
and p.per_habili = '9'
and (at('4',p.per_zonas) >0)
and p.per_hrde <='14:00' and p.per_hra>='14:00'
and (at(LEFT('903',1),p.per_mnu) > 0)
)
and cal.ccperrev+dtos(cal.ccfecrev) = p.per_cve+'20050930'
and act.acgruact+act.acacto = alltrim(cal.ccacto)
and cal.ccfechamax <> cal.ccfeclis
group by cal.ccperrev
Bot of those sqls should be quicker than your original sql. But it's slower still.
I guess that you can try the below sql:
select cal.ccperrev, sum(act.acpuntos)
from r_perso p, r_actos act, r_calcab cal
where (at('CER',p.per_gpos) > 0)
and p.per_habili = '9'
and (at('4',p.per_zonas) >0)
and p.per_hrde <='14:00' and p.per_hra>='14:00'
and (at(LEFT('903',1),p.per_mnu) > 0)
and cal.ccperrev = p.per_cve
and cal.ccfecrev = {d '2005-09-30'}
and act.acgruact+act.acacto = alltrim(cal.ccacto)
and cal.ccfechamax <> cal.ccfeclis
group by cal.ccperrev
|
Re:Re:Re:Re:problem with subquerys |
Carlos Alva |
2006-05-24 12:20:42 |
I really want to be thankful by the excellent support offered by you, your answers are very fast and truely effective, I proved your querys, first of them improved the time, but still it was too slow, approximately 1 minute, the second query worked excellent, returns the result in less than 1 second.
I believe that with this support really you guarantee the purchase of your products, congratulations and follow thus.
|
|
|
Main
webmaster@hxtt.com