Subject | Re: [firebird-support] How to Convert this DBISAM SQL into Firebird SQL ? |
---|---|

Author | Radu Sky |

Post date | 2006-06-03T08:49:02Z |

Manuel Salinas wrote:

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

> Hi,Select H.Numero,

>

> 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.

>

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