Recently, I have found myself dealing with a customer which has a
highly customized software system running on their AS400, which has
caused me much grief. However, during this phase I have learned quite a
bit imbedded RPG SQL, and basic SQL queries. I am mainly writing this
stuff down so that I will remember in the future. I figured it might
help someone else along the way, so here goes....
Today, I
applied some queries to figure out some complex data scenarios in the
system. Basically, I needed to join 2 files together to compare them.
The files had the same record format and field names (one was basically
a copy of the other, but with a different AS400 file name). In STRSQL
on the AS400, I performed statements similar to the below:
select *
from file1 a
join file2 b on
a.field1||a.field2||a.field3 =
b.field1||b.field2||b.field3 where
a.field4 <> b.field4
order by a.field1, a.field2
In the above statement, I
used the identifier "a" for file1, and "b" for file2. This allows SQL
to recognize which file you are referencing in the other statements. In
plain English, the above statement will join file1 & file2 together
where field1, field2, & field3 concatenated together is the same between both files. Once the join is established, only records where
field4 from the first file does not equal field4 from the 2nd file.
The concatenation of fields is a very powerfull tool I didn't realize
existed until recently, so I hope that information is helpful to
someone.
No comments:
Post a Comment