Manuel Salinas wrote:

> Hi,
>
> Can somebody help my how to convert this SQL (DBISAM) into Firebird SQL?
> ----------------------------------------------------------------
> Select H.Numero,
> Sum(if (H.concepto=1 then cast(H.valor as float) else cast(0 as
> float))) as V1,
> Sum(if (H.concepto=2 then cast(H.valor as float) else cast(0 as
> float))) as V2,
> Sum(if (H.concepto=13 then cast(H.valor as float) else cast(0 as
> float))) as V13,
> Sum(if (H.concepto=14 then cast(H.valor as float) else cast(0 as
> float))) as V14,
> Sum(if (H.concepto=16 then cast(H.valor as float) else cast(0 as
> float))) as V16,
> C.Apelp, C.Apelm, C.Nombre
> From HistMov H, NomCatem C
> where H.Numero = C.Numero
> group by H.numero
> ----------------------------------------------------------------
>
>
> This SQL Statement generates COLUMNS for the field "Concepto". In the
> case of Concepto with the values of 1,2,13,14, or 16 does not exist,
> the column will be 0. In other words, the result of values for
> Concepto needs to be in COLUMNS.
>

Select H.Numero,
COALESCE((SELECT SUM Sum(H.valor) WHERE H.concepto=1),0) AS V1,
COALESCE((SELECT SUM Sum(H.valor) WHERE H.concepto=2),0) AS V2,
.....

You can do the casting as you wish
A SP could do this in one parse, so it could be more efficient

HTH
Radu