Re: SQL: CASE WHEN EXISTS Problem

["Lennon_s_j@xxxxxxxxxxx" <lennon_s_j@xxxxxxxxxxx>]

Could it be two (or more) records in SYSIBM.SYSDUMMY1?  There should be 
just one.

Sam

On 3/18/2011 3:12 PM, Tom E Stieger wrote:
> I have an SQL view that is basically doing the following, in addition to a bunch of other stuff:
>
> SELECT
> CASE 	WHEN (SELECT 1 FROM SYSIBM.SYSDUMMY1) IS NOT NULL
> 	THEN 'I am awesome'
> 	ELSE 'The computer has won' END
> FROM SYSIBM.SYSDUMMY1
>
> This was working fine until the subselect ended up being 2 rows such as this:
>
> SELECT
> CASE 	WHEN (SELECT 1 FROM SYSIBM.SYSDUMMY1
> 		UNION SELECT 2 FROM SYSIBM.SYSDUMMY1 ) IS NOT NULL
> 	THEN 'I am awesome'
> 	ELSE 'The computer has won' END
> FROM SYSIBM.SYSDUMMY1
>
> This gives the following Message: [SQL0811] Result of SELECT more than one row.
>
> It looks like I should be able to use an EXISTS predicate, but I can't seem to get that to work either.
>
> SELECT
> CASE 	WHEN EXISTS (SELECT 1 FROM SYSIBM.SYSDUMMY1)
> 	THEN 'I am awesome'
> 	ELSE 'The computer has won' END
> FROM SYSIBM.SYSDUMMY1
>
> This gives the follwing Message: [SQL0104] Token EXISTS was not valid. Valid tokens:<IDENTIFIER>  <INTEGER>  <CHARSTRING>  <GRAPHSTRING>
>
> Using IN didn't work either, giving the following Message: [SQL0115] Comparison operator IN not valid.
>
> I'm not quite sure what else to try, any help would be appreciated.  Thanks.
>
> -Tom Stieger
> California Fine Wire