Subject RE: [firebird-support] Simple sql query question
Author Werner Cloete
Yes, I agree that a Stored Procedure will probably work the best... I
know, for example, that SQL Server allows you to do "case" statements -
which comes in very handy when you want to do/display/calculate
something if a field/value is, for example, NULL... I do not,
unfortunately, remember exactly how you would do this in Firebird...but
the idea goes as follows:

If you have table "MyTable" with fields "Field1" and "Field2":

select Field1, MyNewField = case
when (Field1 = 1)
then <Do your calculation here>
else <Do something else here (you could
leave this part out)
from MyTable
where <Your requirements here>

You would have noticed that the second selected field, "MyNewField",
does not appear in table "MyTable"... This is because I'm selecting the
calculated value into that variable...

I hope that this will help...

Yours sincerely


-----Original Message-----
From: Alan McDonald [mailto:alan@...]
Sent: 05 November 2004 13:04
Subject: RE: [firebird-support] Simple sql query question

> Hello,
> I am wondering is it possible to get this result without writting UDF.
> There is a table:
> Field1 Field2
> ---------------
> 1 2
> 1 1
> 1 3
> 2 4
> 2 1
> 2 3
> ---------------
> I need to get in one recod sum() of Field2 according to Field1. Wanted
> result:
> ResField1 ResField2
> ---------------------
> 6 8
> ---------------------
> 6 = 2+1+3 (Field1 = 1)
> 8 = 4+1+3 (Field2 = 2)

You don't need a UDF - just write yourself an SSP.
Doa FOR SELECT INTO loop ordering Field1, then add up the values of
while the Field1 value remains the same, then move on... SUSPENDing the
results as you go

Yahoo! Groups Links

This e-mail message is confidential and intended solely for the person to whom or the entity to which it is addressed. All the contents and any attachments remain the property of VR Services (Pty) Ltd unless so stated by contract.
If you are not the intended recipient, you are prohibited from reading, copying, using or disclosing this message to others.
If you received this message in error, please notify the sender immediately by replying to this e-mail or by telephoning +27 21 430 9300 and thereafter delete the message. VR Services (Pty) Ltd does not accept liability for any personal views expressed in this message.