× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Paul-

Thanks for the suggestion. I'm not sure if I want to fit that logic into my huge casse statement. I think I will use the count function instead. Something like this seems to work:

SELECT
CASE WHEN (SELECT count(1) FROM SYSIBM.SYSDUMMY1) >= 1
THEN 'I am awesome'
ELSE 'The computer has won' END
FROM SYSIBM.SYSDUMMY1

I still am not sure why the EXISTS predicate doesn't seem to work.

-Tom

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Morgan, Paul
Sent: Friday, March 18, 2011 12:45 PM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: CASE WHEN EXISTS Problem

Tom,

Use IFNULL instead of CASE?

SELECT IFNULL( ( SELECT 'I am awesome'
FROM SYSIBM/SYSDUMMY1
WHERE EXISTS( SELECT 1 FROM SYSIBM/SYSDUMMY1
UNION
SELECT 2 FROM SYSIBM/SYSDUMMY1 ) ),
'The computer has won')
FROM SYSIBM/SYSDUMMY1

Paul Morgan

Principal Programmer Analyst
IT Supply Chain/Replenishment

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tom E Stieger
Sent: Friday, March 18, 2011 3:13 PM
To: 'Midrange Systems Technical Discussion'
Subject: SQL: CASE WHEN EXISTS Problem

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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.