Re: An SQL question...

["R. Bruce Hoffman, Jr." <rbruceh@xxxxxxxxxxxxx>]

-----Original Message-----
From: Rich Duzenbury <rduz@westernmidrange.com>
To: RPG400-L <RPG400-L@midrange.com>
Date: Friday, March 09, 2001 2:52 PM
Subject: An SQL question...


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


*** NOTE *** Short and possibly unworkable hint follows:

Actually, what you want is called a correlated sub-query. First, note that
you MUST be at V4R3 or above for this to work.

update file1 a set last = (select max(last) from file2 b where a.key =
b.key)

The a and b are correlation names and serve to identify what files the
fields come from in the sub-query.

Now for the fun. Since you want to update three fields from three fields,
you have have to assemble them in the subselect (inside the parens) and then
disassemble them in the outer select.  Easier to say than do. But if you are
looking for a maximum date value, you have to assemble them for the test in
the sub-query anyway.

But that is left as an exercise for the reader...  <vbg>


===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified Specialist - AS/400 Administrator
 -- IBM Certified Specialist - RPG IV Developer

"America is the land that fought for freedom and then
  began passing laws to get rid of it."

     - Alfred E. Neuman



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