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]

Re: case statement

meissers@inter. net.il (mmm) wrote in message news:<95dfaef1. 0406010545.382f 19a6@posting.go ogle.com>...[color=blue]
> 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[/color]

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.sysdummy 1;

[mmm]

Re: case statement

jbapplewhite@ae p.com (Jeff) wrote in message news:<15ffd666. 0406011227.5861 16bf@posting.go ogle.com>...[color=blue]
> meissers@inter. net.il (mmm) wrote in message news:<95dfaef1. 0406010545.382f 19a6@posting.go ogle.com>...[color=green]
> > 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[/color]
>
> 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.sysdummy 1;[/color]


Hello,

I am using AS400/DB2 - should it (using the sysibm.sysdummy 1) 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.sysdummy 1 view is better for
performance?

Thanks a lot

[Serge Rielau]

Re: case statement

mmm wrote:
[color=blue]
> jbapplewhite@ae p.com (Jeff) wrote in message news:<15ffd666. 0406011227.5861 16bf@posting.go ogle.com>...
>[color=green]
>>meissers@inte r.net.il (mmm) wrote in message news:<95dfaef1. 0406010545.382f 19a6@posting.go ogle.com>...
>>[color=darkred]
>>>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[/color]
>>
>>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.sysdummy 1;[/color]
>
>
>
> Hello,
>
> I am using AS400/DB2 - should it (using the sysibm.sysdummy 1) 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.sysdummy 1 view is better for
> performance?
>
> Thanks a lot[/color]

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.SYSDUMMY 1. 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]

Re: case statement

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

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab