Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT DBF
problem with subquerys
Carlos Alva
2006-05-23 15:45:02.0
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.0
>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.0
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.0
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.0
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.

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap