Thanks for your help Helen.
I am so sorry that I forgot to specify the kind of error.
It gives "Dynamic SQL Error" as :

"Invalid token.Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 20, char 4.
when."

I correct the missing comma and quatos.
h.hesap_sec_ tablo is constrained by NOT NULL.
But I'm not sure if I understood true one part of your message, which was:

"3) Run-time: you potentially have a run-time error through your use
of subqueries in your CASE predications, too, if your subqueries
happen to return multiple results (subquery expressions MUST return a
singleton). However, if m.MUS_ID and pk.PER_ID are unique, there is
no risk of a run-time error from these subqueries."

MUS_ID is the Primary Key of Musteri_Karti table, and PER_ID is also the Primary Key of Personel_Kart table. If one field is a primary key of a table, that means this field is 'unique', am I right?

I just try to write the code as you wrote, but it still gives same error that I mentioned above. I think I am missing same important part but I don't know where to concentrate on.









----- Original Message ----
From: Helen Borrie <helebor@...>
To: firebird-support@yahoogroups.com
Sent: Saturday, November 25, 2006 12:04:36 AM
Subject: Re: [firebird-support] CASE WHEN error

At 04:15 AM 25/11/2006, you wrote:

>Thank you for your interest. I don't have enough experience about
>Firebird SQL. My graduation project was about a law office program.
>I didn't have any problem during the presentation. Now I have to
>deal with that program again but it gives an error about CASE WHEN statement.
>
>Tables' names are in Turkish, so I think its better to write the
>table names first, in order to help you to understand.

It's also simpler when using joins to employ table aliases...

>Table Name: Fields:
>
>HESAPLAR HESAP_ID
> HESAP_TARIHI
> HESAP_SEC_TABLO
> HESAP_SEC_TAB_ ID
>PERSONEL_KART PER_ID
> PER_ISIM
> PER_SADI
>MUSTERI_KARTI MUS_ID
> MUS_ISIM
> MUR_SADI
>
>
>And this is the code that gives error at CASE WHEN statement:

You don't say what kind of error is occurring. "..gives an error.."
is too unspecific to be useful in troubleshooting anything.

>select DISTINCT HESAPLAR.HESAP_ ID,
> HESAPLAR.HESAP_ TARIHI,HESAPLAR. hesap_sec_ tablo,
> HESAPLAR.HESAP_ SEC_TAB_ID,
> CASE when hesaplar.hesap_ sec_tablo = 0
> then (select trim(PERSONEL_ KART.PER_ ISIM) ||'
> '||trim(PERSONEL_ KART.PER_ SADI)
> from personel_kart
> where personel_kart. per_id=hesaplar. hesap_sec_ tab_id)
> else (select trim(MUSTERI_ KARTI.MUS_ ISIM) ||'
> '||trim(MUSTERI_ KARTI.MUS_ SADI)
> from musteri_karti
> where musteri_karti. mus_id =hesaplar.hesap_ sec_tab_id)
> end as kisi,hesaplar. hesap_aciklama, hesaplar. hesap_dekonttur,
>HESAPLAR.HESAP_ TUR_ID,hesaplar. hesap_debit, hesaplar. hesap_credit
>case when hesaplar.hesap_ sec_tablo = 0 then ''P'' Else ''M'' end as ST
>from hesaplar

The most obvious errors here are

1) Syntax: a missing comma after the penultimate output field and

2) Syntax: the use of double pairs of single quotes for the outputs
'P' and 'M'. If you want the output to be simply the character, use
just one pair of single quotes, to delimit them as strings. If you
want the output to include literal single quotes (as apostrophes) ,
you need THREE pairs: the outer pair as the string delimiter plus
one preceding each literal apostrophe (referred to as "doubling")

3) Run-time: you potentially have a run-time error through your use
of subqueries in your CASE predications, too, if your subqueries
happen to return multiple results (subquery expressions MUST return a
singleton). However, if m.MUS_ID and pk.PER_ID are unique, there is
no risk of a run-time error from these subqueries.

4) Run-time: if h.hesap_sec_ tablo is not constrained by NOT NULL, you
have a potential for your CASE conditions to return WRONG results

select DISTINCT
h.HESAP_ID,
h.HESAP_TARIHI,
h.hesap_sec_ tablo,
h.HESAP_SEC_ TAB_ID,
CASE when h.hesap_sec_ tablo = 0
then
(select trim(pk.PER_ ISIM) ||' '||trim(pk.PER_ SADI)
from personel_kart pk
where pk.per_id=h. hesap_sec_ tab_id)
else
(select trim(mk.MUS_ ISIM) ||' '||trim(mk.MUS_ SADI)
from musteri_karti mk
where mk.mus_id =h.hesap_sec_ tab_id)
end as kisi,
h.hesap_aciklama,
h.hesap_dekonttur,
h.HESAP_TUR_ ID,
h.hesap_debit,
h.hesap_credit, /* <<----- MISSING COMMA */
case when h.hesap_sec_ tablo = 0 then 'P' Else 'M' end as ST
from hesaplar h

..or, if you want to return literal quotes on 'P' or 'M':

case when h.hesap_sec_ tablo = 0 then '''P''' Else '''M''' end as ST

./heLen






____________________________________________________________________________________
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com

[Non-text portions of this message have been removed]