Jules Bean wrote:
>
> Bernhard Thoni wrote:
> >
> > first of all: thanx for helping
>
> It's best to keep the list in the Cc: of the mails - others will know
> more than me about many things!
>
> > ok: iif seems to be a special kind of if (i know this from visual
> > basic). here comes the statement i have to port that it works under
> > linux with mysql client:
>
> OK. I think IIF is a red herring - it's nothing to do with JOINs. It
> seems to have similar semantics to the mysql function IF(), so I expect
> you can replace it with them.
>
> >
> > SELECT
> > dbo_Kunden.K_Name1, dbo_Kunden.K_Name2,
> > Calls.User AS Ausdr1, dbo_AuftragPos.AuP_M_Id,
> > Sum([acct_session_time]/60) AS OnlineZeit,
> > Sum([acct_input_octets]*0.0009765625*0.0009765625) AS Input,
> > Sum([acct_output_octets]*0.0009765625*0.0009765625) AS Output,
> > [Input]+[Output] AS Transfer,
> >
> > IIf([AuP_M_Id] Like 0 Or [AuP_M_Id] Like
> > 2,[OnlineZeit]-180,IIf([AuP_M_Id] Like 3,[OnlineZeit]-1200)) AS
> > Zeitueberschreitung,
> > IIf([AuP_M_Id] Like 0 And
> >
> [Zeitueberschreitung]>0,[Zeitueberschreitung]*0.07,IIf([Zeitueberschreitung]>0,[Zeitueberschreitung]*0.0812))
> > AS Kosten_Zeit,
> > IIf(([AuP_M_Id] Like 2 Or [AuP_M_Id] Like 3 Or [AuP_M_Id] Like 4) And
> > ([Transfer]-200)>0,[Transfer]-200, IIf(([AuP_M_Id] Like 5) And
> > ([Transfer]-1024)>0,[Transfer]-1024)) AS TransUeber,
> > IIf([TransUeber]>0,[TransUeber]*0.928) AS KostenTransfer,
> > Nz([Kosten_Zeit])+Nz([KostenTransfer]) AS Summe_DM
> >
>
> This is the JOINs bit here.
>
> This bit should work absolutely fine.
>
> > FROM Calls INNER JOIN ((dbo_Kunden INNER JOIN dbo_Auftrag ON
> > dbo_Kunden.K_Id = dbo_Auftrag.Au_K_Id)
> > INNER JOIN dbo_AuftragPos ON dbo_Auftrag.Au_Id = dbo_AuftragPos.AuP_Id)
> > ON Calls.User = dbo_Kunden.K_MATCH
> >
> > WHERE (((dbo_Auftrag.Au_StartDatum)<=[Calls]![Start]) AND
> > ((dbo_Auftrag.Au_StopDatum)>=[Calls]![Stop]))
> >
> > GROUP BY dbo_Kunden.K_Name1, dbo_Kunden.K_Name2, Calls.User,
> > dbo_AuftragPos.AuP_M_Id
> > HAVING (((dbo_AuftragPos.AuP_M_Id)=0 Or (dbo_AuftragPos.AuP_M_Id)=2 Or
> > (dbo_AuftragPos.AuP_M_Id)=3 Or (dbo_AuftragPos.AuP_M_Id)=4 Or
> > (dbo_AuftragPos.AuP_M_Id)=5))
> > ORDER BY dbo_AuftragPos.AuP_M_Id, dbo_Kunden.K_Name1
> > ;
> >
>
> > i am not sure how to do the iif block in mysql-environment (i read that
> > there is no "select .. from ... where (select ...)";
>
> There is none, you are correct. However, I wouldn't have thought you
> needed one - those are row-expressions (scalar expressions evaluated in
> the context of each row) not relational expressions, as far as I can
> see.
>
> > what do you think, is it normal to take 2 hours for such a statement ?
> > we have about 80000 lines in table Calls, in the others very few
>
> Definitely not. You probably don't have enough indexes in place.
>
> The mysql team will want to see the output of EXPLAIN SELECT ... so they
> can tell you why the indexing isn't working.
>
> Jules
Hi Jules
AFAIK you can't reuse result column labels in other result columns or in the WHERE part.
So you have to replace these with the original definition.
As an exaple I did this for Input and Output in Transfer.
I don't know what Nz does :(
You should use something like:
SELECT
knd.K_Name1
, knd.K_Name2
, c.User AS Ausdr1
, pos.AuP_M_Id
, SUM( [acct_session_time] / 60 ) AS OnlineZeit
, SUM( [acct_input_octets] * 0.0009765625 * 0.0009765625 ) AS Input
, SUM( [acct_output_octets] * 0.0009765625 * 0.0009765625 ) AS Output
, SUM( [acct_input_octets] * 0.0009765625 * 0.0009765625 )
+ SUM( [acct_output_octets] * 0.0009765625 * 0.0009765625 ) AS Transfer
, IF( pos.AuP_M_Id = 0 OR pos.AuP_M_Id = 2
, [OnlineZeit]-180
, IF( pos.AuP_M_Id = 3
,[OnlineZeit]-1200
, NULL
)
) AS Zeitueberschreitung
, IF( pos.AuP_M_Id = 0 AND [Zeitueberschreitung]>0
, [Zeitueberschreitung]*0.07
, IF( [Zeitueberschreitung]>0
, [Zeitueberschreitung]*0.0812
, NULL
)
) AS Kosten_Zeit
, IF( (pos.AuP_M_Id = 2 OR pos.AuP_M_Id = 3 OR pos.AuP_M_Id = 4)
AND ([Transfer] - 200)>0
, [Transfer]-200
, IF( pos.AuP_M_Id = 5 And ([Transfer]-1024) > 0
, [Transfer]-1024
, NULL
)
) AS TransUeber
, IF( [TransUeber] > 0, [TransUeber] * 0.928, NULL) AS KostenTransfer
, Nz([Kosten_Zeit]) + Nz([KostenTransfer]) AS Summe_DM
FROM
Calls AS c
, dbo_Kunden AS knd
, dbo_Auftrag AS auf
, dbo_AuftragPos AS pos
WHERE
knd.K_Id = auf.Au_K_Id
AND auf.Au_Id = pos.AuP_Id
AND c.User = knd.K_MATCH
AND auf.Au_StartDatum <= c.Start
AND auf.Au_StopDatum >= c.Stop
AND ( pos.AuP_M_Id = 0
OR pos.AuP_M_Id = 2
OR pos.AuP_M_Id = 3
OR pos.AuP_M_Id = 4
OR pos.AuP_M_Id = 5
)
GROUP BY
knd.K_Name1
, knd.K_Name2
, c.User
, pos.AuP_M_Id
ORDER BY
pos.AuP_M_Id
, knd.K_Name1
;
With this query you need the following keys (if possible):
Calls -> (User, Start, Stop)
dbo_Kunden -> (K_ID, K_MATCH)
dbo_Auftrag -> (Au_K_Id, Au_Id, Au_StartDatum, Au_StopDatum)
dbo_AuftragPos -> (AuP_M_Id)
Tschau
Christian
