RE: SQL: CASE WHEN EXISTS Problem

Sam-

My orignal SQL view had multiple when clauses in the case staement to output a text string based on the different conditions. In my example I used SYSDUMMY1 to simplify it. In my second SQL it is using a union to get 2 records in the results set.


-Tom

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Lennon_s_j@xxxxxxxxxxx
Sent: Friday, March 18, 2011 3:32 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL: CASE WHEN EXISTS Problem

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.