At 02:30 AM 26/11/2006, you wrote:

>Hello again :)
>I'm so sorry for taking your time Helen and Svein.
>I'm using Firebird v1.5 and SQL Dialect:3.

Have you checked the dialect *of the database* using isql SHOW SQL DIALECT?

>Please check the amended code below:
>
>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,
>case when h.hesap_sec_ tablo = 0 then 'P' Else 'M' end as ST
>from hesaplar h

Looks OK. I do wonder why you have DISTINCT in this query if
HESAP_ID is the primary key of hesaplar. Have you tried the query
without the DISTINCT?

However, if the error message that you reported is the right one for
*this* query, then the query isn't falling over because of the
presence of a CASE statement. It appears to accept the first
occurrence and then choke on the second one with the Invalid Token
WHEN error, as one would expect from your *first* attempt, where you
omitted the comma in line 19.

What is the environment in which you are testing this? Try inserting
a blank line before the lines containing the CASE statements to see
whether the error message returns an invalid token in the new
position...you could also play about with commenting out first one
then the other CASE statement to try to pinpoint the offending
location..and so on.

>If I can the write same code with "IF/ELSE" statement instead of
>"CASE WHEN", would it give me the same result that I want?

You can't use an IF/ELSE statement in dynamic SQL; but, yes, it
would be possible to write a selectable SP where you read the value
of h.hesap_sec_ tablo for each row in a FOR SELECT loop and
manufacture the results for the two derived fields directly. It is
what one did without support for CASE.

However, if you're serious about troubleshooting this, it would be
better to persevere until you find out what is actually causing the
problem. For example, if your database turns out to be Dialect 1,
this won't be the last time you bump into an unsupported feature...

./heLen