case statement

[mmm]

Hello,

I wrote the following statement:
SELECT CASE
WHEN (SELECT count(*) FROM employee where empno='000010')>0 THEN
'true'
ELSE 'false'
END AS isExist
from employee where empno='000010'

My problem is that when the empno exists everything is ok but when it
does not exist I got no record becuase of the last where cluase.

How can I rewrite this statement in order to get true when exists and
false when it does not exist?

Thanks

[Jeff]

me******@inter.net.il (mmm) wrote in message news:<95**************************@posting.google. com>...

Hello,

I wrote the following statement:
SELECT CASE
WHEN (SELECT count(*) FROM employee where empno='000010')>0 THEN
'true'
ELSE 'false'
END AS isExist
from employee where empno='000010'

My problem is that when the empno exists everything is ok but when it
does not exist I got no record becuase of the last where cluase.

How can I rewrite this statement in order to get true when exists and
false when it does not exist?

Thanks

There are a few, but here is one close to what you have:
SELECT CASE
WHEN (SELECT count(*) FROM employee where epmno = '000010') > 0 THEN
'true'
ELSE 'false'
END AS isExist
from sysibm.sysdummy1;

[mmm]

jb**********@aep.com (Jeff) wrote in message news:<15**************************@posting.google. com>...

me******@inter.net.il (mmm) wrote in message news:<95**************************@posting.google. com>...

Hello,

I wrote the following statement:
SELECT CASE
WHEN (SELECT count(*) FROM employee where empno='000010')>0 THEN
'true'
ELSE 'false'
END AS isExist
from employee where empno='000010'

My problem is that when the empno exists everything is ok but when it
does not exist I got no record becuase of the last where cluase.

How can I rewrite this statement in order to get true when exists and
false when it does not exist?

Thanks

There are a few, but here is one close to what you have:
SELECT CASE
WHEN (SELECT count(*) FROM employee where epmno = '000010') > 0 THEN
'true'
ELSE 'false'
END AS isExist
from sysibm.sysdummy1;

Hello,

I am using AS400/DB2 - should it (using the sysibm.sysdummy1) works
the same as other platforms?

I tried to use the following command and it seems good:

SELECT CASE
WHEN (SELECT count(*) FROM employee where empno='000010')>0 THEN
'true'
ELSE 'false'
END AS isExist
from employee group by 'isExist'

Do you think that using the sysibm.sysdummy1 view is better for
performance?

Thanks a lot

[Serge Rielau]

mmm wrote:

jb**********@aep.com (Jeff) wrote in message news:<15**************************@posting.google. com>...

me******@inter.net.il (mmm) wrote in message news:<95**************************@posting.google. com>...

Hello,

I wrote the following statement:
SELECT CASE
WHEN (SELECT count(*) FROM employee where empno='000010')>0 THEN
'true'
ELSE 'false'
END AS isExist
from employee where empno='000010'

My problem is that when the empno exists everything is ok but when it
does not exist I got no record becuase of the last where cluase.

How can I rewrite this statement in order to get true when exists and
false when it does not exist?

Thanks

There are a few, but here is one close to what you have:
SELECT CASE
WHEN (SELECT count(*) FROM employee where epmno = '000010') > 0 THEN
'true'
ELSE 'false'
END AS isExist
from sysibm.sysdummy1;

Hello,

I am using AS400/DB2 - should it (using the sysibm.sysdummy1) works
the same as other platforms?

I tried to use the following command and it seems good:

SELECT CASE
WHEN (SELECT count(*) FROM employee where empno='000010')>0 THEN
'true'
ELSE 'false'
END AS isExist
from employee group by 'isExist'

Do you think that using the sysibm.sysdummy1 view is better for
performance?

Thanks a lot

Actually that select will give you as many rows back as there are in the
employee table whcih is not what you want.
I think Db2 for AS/400 supports SYSIBM.SYSDUMMY1. If it doesn't theer
must be soem other single-row object you can use like VALUES.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

SELECT CASE WHEN count(*) > 0 THEN 'true' ELSE 'false' END FROM employee
where empno='000010'

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab