 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Marcello Dias
Guest
|
 Posted: Wed May 19, 2004 1:25 pm Post subject: Question of performance. |
 |
|
Hi How the folowing query works.
select T1.Cod_Operacao , T1.Cod_Cliente , T1.Nr_Servico,
T1.Ciclo, T1.Nrc,MinMax.MaxVenc,MinMax.MinVenc
into [#Tempo2]
from [#Tempo1] T1
join
(select max(Dt_Vencto) as MaxVenc,min(Dt_Vencto) as MinVenc,
Cod_Operacao , Cod_Cliente , Nr_Servico
from [Faturamento] where Cod_Operacao = @CodOperacao
group by Cod_Operacao,Cod_Cliente,Nr_Servico)
as MinMax
on T1.Cod_Operacao=MinMax.Cod_Operacao
and T1.Cod_Cliente=MinMax.Cod_Cliente and T1.Nr_Servico =MinMax.Nr_Servico
where MaxVenc>=@Hoje
It will calculate all the min and max from the faturamento table
gouped by Cod_Operacao,Cod_Cliente,Nr_Servico
and them join it with the #tempo1 or will calculate only
those records that can be joined with #tempo1.
Regards,
Marcello Dias
|
|
| Back to top |
|
 |
Marcello
Guest
|
| Posted: Wed May 19, 2004 1:44 pm Post subject: Re: Question of performance. |
|
|
"I´m using SQL SERVER 2000
|
|
| Back to top |
|
|
Wayne Niddery [TeamB]
Guest
|
| Posted: Wed May 19, 2004 11:12 pm Post subject: Re: Question of performance. |
|
|
Marcello Dias wrote:
| Quote: |
select T1.Cod_Operacao , T1.Cod_Cliente , T1.Nr_Servico,
T1.Ciclo, T1.Nrc,MinMax.MaxVenc,MinMax.MinVenc
into [#Tempo2]
from [#Tempo1] T1
join
(select max(Dt_Vencto) as MaxVenc,min(Dt_Vencto) as MinVenc,
Cod_Operacao , Cod_Cliente , Nr_Servico
from [Faturamento] where Cod_Operacao = @CodOperacao
group by Cod_Operacao,Cod_Cliente,Nr_Servico)
as MinMax
on T1.Cod_Operacao=MinMax.Cod_Operacao
and T1.Cod_Cliente=MinMax.Cod_Cliente and T1.Nr_Servico
=MinMax.Nr_Servico where MaxVenc>=@Hoje
It will calculate all the min and max from the faturamento table
gouped by Cod_Operacao,Cod_Cliente,Nr_Servico
and them join it with the #tempo1 or will calculate only
those records that can be joined with #tempo1.
|
Because you didn't specify a qualifier for the Join, it is by default an
INNER join which means you will only get results from faturamento where
there is a match in #Tempo1. Futhermore, your Where clause specifies a
conditon on the MinMax result set (MaxVenc>=@Hoje), and so even if you
changed the join to a LEFT OUTER join, it would still be limited to matches
on #TEMPO1. If you need all records from #TEMPO1 to be selected even when no
match in MinMax, then change to LEFT OUTER join and move the condition
(MaxVenc>=@Hoje) into the join condition instead of the Where clause.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr.
|
|
| Back to top |
|
|
Marcello Dias
Guest
|
| Posted: Mon May 24, 2004 7:21 pm Post subject: Re: Question of performance. |
|
|
"Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote:
tHANK yOU.
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
