----- Original Message -----
From: "Arno Brinkman" <firebird@...>
To: <firebird-support@yahoogroups.com>
Sent: Saturday, August 23, 2003 7:54 PM
Subject: Re: [firebird-support] Select query from IB6.0 to FB 1.5 RC4


> Hi Luiz,
> You've got an invalid expression in your select list :-)
>
> Let's make your query more readable :
>
> SELECT
> B.isdiu,
> C.Nome as Medico,
> F.Nome as Servico,
> sum(A.Stotal) as Tot_clin,
> (SELECT count(S1.Nr) FROM Porc_med S1
> WHERE S1.Nr = C.Nr and S1.porcD>0) as Fator_div,
> ^^^^^^
> D.porcD as PorcMedico,
> C1.TOT_DESP as Despesa,
> sum(A.stotal*D.porcd/100) as Part_MED,
> sum(A.stotal*(D.porcd/100)*E.IR/100) as IR,
> sum(A.stotal*(D.porcd/100)*E.ISS/100) as ISS,
> sum(A.stotal*(D.porcd/100)*(100-E.IR-E.ISS)/100) as LIQ,
> C1.TOT_DESP*D.porcd/100.0/(SELECT count(S1.Nr)
> FROM Porc_med S1
> WHERE S1.Nr = C.Nr and S1.porcD>0) as Desp_med,
> ^^^^^
> sum(A.stotal*(D.porcd/100)*(100-E.IR-E.ISS) /
> 100 * C1.GLOSA / 100) as GLOSA
> FROM
> Medint C
> left join GRUPO_MED C1 on C1.Nr=C.Nr and C1.Diurno='T'
> join relfat B on C.Nr=B.medint
> join fatura A on A.nrf=B.nrf and A.stipo<>4
> join porc_med D on D.Nr=C.Nr and D.nserv=A.stipo
> join servbase F on A.stipo=F.nserv
> join Planos E on E.ident_cp=B.ident_cp
> WHERE
> B.Data>=:di and
> B.Data<:df and
> C.Nr=:v_med
> GROUP BY
> C.Nome,
> B.isdiu,
> F.Nome,
> C1.TOT_DESP,
> D.porcd
> ORDER BY 2,1 desc,3
>
>
> C.Nr isn't part of the GROUP BY clause and due bugs inside FB1.0 IBx you
can
> get even wrong results with this query!
> Add C.Nr to the GROUP BY clause or change it into a aggregate function
> MAX(C.Nr), MIN(C.Nr) depending on what it should be.
>
> Regards,
> Arno Brinkman
> ABVisie
>