Subject | Re: How to Convert this DBISAM SQL into Firebird SQL ? |
---|---|

Author | Manuel Salinas |

Post date | 2006-06-03T04:11:46Z |

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

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.

>Firebird SQL?

> --- In firebird-support@yahoogroups.com, "Manuel Salinas"

> <msalinas_1999@> wrote:

> >

> > Hi,

> >

> > Can somebody help my how to convert this SQL (DBISAM) into

> > ----------------------------------------------------------------Hi Mikcaau,

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

>

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.