Re: Sql Question

Dang. here I sit, corrected. :) It was about time, since my last prior
correction was over ten minutes ago.

From Wikipedia: http://en.wikipedia.org/wiki/Null_%28SQL%29

SELECT *
FROM sometable
WHERE num <> 1; -- Rows where num is NULL will not be returned,
-- contrary to many users' expectations.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Nuclear war can ruin your whole compile."
-- Karl Lehenbauer

If I have screwed this up I can't see where.

Select t1.ID,
t1.AS400_PROFILE,
t1.INITIALS,
t2.ID,
t2.AS400_PROFILE,
t2.INITIALS
From G2STAGE2/USER_PROFILE t1
Inner Join G2PROD/USER_PROFILE t2
On t1.AS400_PROFILE = t2.AS400_PROFILE
Where t1.INITIALS <> t2.INITIALS

This find nothing. Output from this query below.

Select t1.AS400_PROFILE,
t1.INITIALS,
t2.AS400_PROFILE,
t2.INITIALS
From G2STAGE2/USER_PROFILE t1
Inner Join G2PROD/USER_PROFILE t2
On t1.AS400_PROFILE = t2.AS400_PROFILE
Where T1.AS400_PROFILE =
'FUEL500'

User User User
User
Profile Initials Profile
Initials
FUEL500 FUL FUEL500
-

If I add the following I get a difference.

Select t1.ID,
t1.INITIALS,
t1.AS400_PROFILE,
t2.INITIALS,
t2.AS400_PROFILE,
From G2STAGE2/USER_PROFILE t1
Inner Join G2PROD/USER_PROFILE t2
On t1.AS400_PROFILE = t2.AS400_PROFILE
Where (t1.INITIALS <> t2.INITIALS) Or
(t1.INITIALS Is Null And t2.INITIALS Is Not Null) Or
(t1.INITIALS Is Not Null And t2.INITIALS Is
Null)


On Tue, Aug 17, 2010 at 4:50 PM, Dennis Lovelady
<iseries@xxxxxxxxxxxx>wrote:

See inline

I have an SQL statement that compares data in for one table in two
different
libraries. The SQL just says report any record where the initials

are

different but when I run it I get nothing.

I had one record where the initials for in one record was Null and

in

the
other FUL but the SQL reported them as equal. That doesn't make

much

sense.

Agreed.

Does SQL bypass the test if one side is null?

No, it doesn't do that.

Isn't null just a absence of
data so they would not be equal?

That's right.

Have I found a bug (A big one?

Probably not. More likely it's an error in your JOIN or WHERE or

whatever

you're doing to combine/compare the two tables. Since you have

elected not

to share any code, it's difficult to go further.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
In some cultures, what I do would be considered normal.


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

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

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