--- In firebird-support@yahoogroups.com, "mikcaau" <macomp@...> wrote:

>
> --- In firebird-support@yahoogroups.com, "Manuel Salinas"
> <msalinas_1999@> 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.
> >
> > Can somebody help me with this?
> >
> > Thanks in advance.
> >
> Case when (H.concepto=1 then cast( H.Valor as float) else cast(0 as
> float) as V1
>
> or similar.
>

Hi Mikcaau,

It does not work... the CASE Statement has only one "else" for all the
conditions. I need one "else" for each condition.....

Here is an example of the Data ("Numero" 2 does not have "Concepto" 13):

Numero Concepto Valor
1 1 5.00
1 2 1.00
1 13 1.00
1 14 1.00
1 16 1.00
2 1 6.00
2 2 2.00
2 14 2.00
2 16 2.00

and here is the result I need to get:

Numero V1 V2 V13 V14 V16
1 5.00 1.00 1.00 1.00 1.00
2 6.00 2.00 0.00 2.00 2.00

The value (V13) for the "Numero" 2 equals to "0.00", because there is
not "valor" for "Numero" 2.

DBISAM does it with the SQL writed above.... Is it possible to get the
same result with a SQL in Firebird?.....

Again, any help will be appreciated.

Regards.