Left Outer Join Issue

This is a post in continuation of my previous post where I had described an issue I had faced with Outer join. And the solution that I had mentioned at the end of it was not correct. In fact the query that I had put in was also incomplete. Following line describes the issue and the solution. Consider two tables

Employee - 

Employee (Number) - (10 distinct records)
Department (Char)

Manager -

Employee Number (Char) - (3 records matching with TableA)
Employee Name(Char)
Manager (char)

Requirement:- Select the list of all employee numbers,Employee Name. For normal employees, employee name can be blanks while for managers select the correct name from Manager table.

I wrote the following query to achieve the same using a Left Outer Join.

Select A.EmpNo,ifnull(B.EmpName,' ') from Employee A Left Outer Join

Manager B On A.EmpNo = B.EmpNo Where B.IsManager = 'Y'

This query should ideally return 10 records since a Left Outer Join is done. But here
the query would return only 8 records i.e. it would function like an Inner Join.

Why:- Here the where condition is applied on the Joined table. Since for the mis- matching rows the value is null, the where condition would fail hence that row would be omitted. Here is an alternate solution

Select A.EmpNo,ifnull(B.EmpName,' ') from Employee A Left Outer Join

(Select * From Manager Where IsManager = 'Y') B On A.EmpNo = B.EmpNo

This would work as there is no where condition applied on the null columns.

Thanks Lynne Roll and all other System i Network users for this piece of learning.

Click here to go to the System i Network thread. ; Forum Post

Left Outer Join Behaving Crazy.

I had this requirement where I had TableA and TableB where I need to update a field FieldA in TableA with the value in TableB for matching records based on FieldB. If there are no matching records then I need to update the same with 0. And the fieldB based on which I have to match the records in two tables are in two different formats(TableA -Char, TableB - Int) After figuring out that Left Out Join is the best option to solve this problem I wrote this query.

Select IFNULL(B.FieldB,0)B.FieldB from TableA A
Left Outer Join TableB B On int(A.FieldB) = B.FieldB

But on running the query i found that only the matching records from TableB were getting selected. The Left Outer Join behaved just like an Inner Join.

Finally It worked only when i wrote a subquery first to convert the FieldB in TableA and then used that table to do an Outer Join with TableB.

About the Machine

The AS400 or now called as the IBM System i belongs to the genus of the System/38 database machine architecture launched by IBM in 1979. AS400 is commonly confused with the IBM Mainframe systems which also has a Black and Green screen like AS400. The AS400 was the first general purpose machine to attain a C2 security rating from NSA. In 2000 IBM renamed the AS400 to iSeries as part of e-server branding initiative. The architecture of the system allows for future implementation of 128-bit processors when they become available. Existing applications can use the new hardware without modification. There are some very interesting advertisements of IBM AS400 which totally projects the versatility of the system. Here is one of those videos.

IBM e-Server Ad

The first post.

Being the first post in my tech blog I hope I will be able to make this an informative page for all the AS400 enthusiasts and programmers. I am Anil myself an AS400 programmer working on AS400 system for the last 2 years. The articles on this blog are purely based on my understanding of the system which can possibly be incomplete or incorrect as I am also in the process of learning. I kindly request you to put in your suggestions and comments about the contents of this page.