Re: Weird results from WHERE clause ...

There were some times in the past, when I've had issue with select * from a
CTE. I wonder if you've tried replacing the select *, with a select
against explicit columns. I realize that your original statement should
work, but it might give you additional information for IBM, and a
work-around.




From: Jon Paris <jon.paris@xxxxxxxxxxxxxx>
To: Midrange-L <midrange-l@xxxxxxxxxxxx>
Date: 05/12/2016 10:05 AM
Subject: Weird results from WHERE clause ...
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



OK SQL gurus - need your help here. I’m pretty sure this has to be an SQL
bug but if so I’d have expected it to have been reported before now and I
can’t find anything on IBM’s site that sounds similar.

Have a large complex CTE which produces a weird result if I use = in the
WHERE compared with BETWEEN.

For example SELECT * from MyCTE where UniqueId between 50 and 50 produces
the expected single row result with all columns correctly populated.

On the other hand SELECT * from MyCTE where UniqueId = 50 also produces a
single row but all columns are empty/null. Needless to say the results
should be identical.

Originally this was run on a 6.1 system so it was moved to a pretty current
7.1 box with identical results.


Anyone seen this or anything like it?


Jon Paris

www.partner400.com
www.SystemiDeveloper.com

--
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.

Please contact support@xxxxxxxxxxxx for any subscription related questions.