Re: An SQL question...

Hi Rich,

I'm just beginning to figure out SQL myself (meaning I'm no expert!), but 
I think I might be able to help here.

My approach would be to break the problem up into several smaller problems:

1) How do I find the maximum create date per person in in File2?
Answer: first, get the create date into a better format (year, month day
all in one field). Since they're all numbers, you could do something like:
SELECT AREFNO, CRTYY, CRTMM, CRTDD, ((CRTYY*10000) +(CRTMM*100) +CRTDD) as
newCreateDate FROM File2.
(if they weren't numbers, you would use some sort of concatenation
function)
Second, what you want to end up with is SELECT AREFNO, CRTYY, CRTMM,
CRTDD, MAX(newCreateDate) FROM File2 GROUP BY AREFNO, CRTYY, CRTMM, CRTDD.
Getting MAX(newCreateDate) may be tricky, since this is not a field in the
file -- again, I'm no expert, but I think one of the following should work:
a) use MAX(((CRTYY*10000) +(CRTMM*100) +CRTDD)) as newCreateDate) and see
if it flies
b) otherwise, SELECT AREFNO, CRTYY, CRTMM, CRTDD, MAX(newCreateDate) from
(SELECT AREFNO, (((CRTYY*10000) +(CRTMM*100) +CRTDD)) as newCreateDate
FROM File2) GROUP BY AREFNO CRTYY, CRTMM, CRTDD, (this is called using a
sub-select) 
2) Once I have the maximum date, how do I use it?
The simplest (but not necessarily most elegant) answer is probably to
create a new table (File3) and then change the previous SELECT statement
into an INSERT statement to fill File3 with the results. Then the
following second statement should work:
UPDATE File1.RECYY=File3.CRTYY, File1.RECMM=File3.CRTMM,
File1.RECDD=File3.CRTDD WHERE File1.AREFNO=File3.AREFNO
I think in theory it should be possible to avoid having to create a new
table by having it all happen in one big statement, but I'm not sure how
to do it, and I personally would wonder whether it's worth the effort just
to prove the theory.

Somebody else may come up with a better answer, though. . . .

hth

RPG400-L@midrange.com writes:
>I'm trying to get more proficient with AS/400 SQL.  I need to update one 
>file from another, and I can't figure out if it's even possible (see
>below 
>for why).  Once I figure out the statement, I can make it an embedded
>SQLRPG.
>
>Please be aware that I didn't design these file structures, but have to 
>work within them.
>
>File 1 is a person file (most fields omitted for clarity).  A person can 
>have multiple accounts, which are stored in file2 (again, most fields 
>omitted for clarity).  The date fields in File1 are supposed to store the 
>most recent account date.  As an account is created for a person, the 
>person file should be updated with the latest date.
>
>Due to a software bug (not to mention poor design), the person file 'most 
>recent account date' fields are out of sync with the actual latest
>account 
>date.  Normally, I would just write a level break program over file2, and 
>in about fifteen lines of code, I could update the person file, and have 
>everything in tip top shape.
>
>But, the goal of todays exercise is to see if it's possible with SQL. 
>The 
>big question is how to select the account with the highest create date, 
>since the designers chose to store the date components in separate fields?
>
>UPDATE FILE1 SET RECYY=CRTYY, RECMM=CRTMM, RECDD=CRTDD WHERE ?????
>
>File1: (Person file, Uniquely Keyed by REFNO)
>REFNO   10/0 S  Unique Key for file (Person Number)
>RECYY   4/0 S   Most Recent Account Year
>RECMM   2/0 S   Most Recent Account Month
>RECDD   2/0 S   Most Recent Account Day
>
>File2: (One Person Can have many Accounts)
>ACCTNO  15/0 P  Unique Key for File (Account Number)
>AREFNO  10/0 S  Foreign Key to File1 (Person Number)
>CRTYY   4/0 S   Account Creation Date Year
>CRTMM   2/0 S   Account Creation Date Month
>CRTDD   2/0 S   Account Creation Date Day
>
>Thank you.
>
>Regards,
>Rich
>
>+---
>| This is the RPG/400 Mailing List!
>| To submit a new message, send your mail to RPG400-L@midrange.com.
>| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
>| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
>| Questions should be directed to the list owner/operator:
>david@midrange.com
>+---



Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA  01376
413-863-4357 x444
mnaughton@juddwire.com

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---