Re: An SQL question...

quick follow up...

you could create views over the date fields to combine them...


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

-----Original Message-----
From: R. Bruce Hoffman, Jr. <rbruceh@attglobal.net>
To: RPG400-L@midrange.com <RPG400-L@midrange.com>
Date: Friday, March 09, 2001 4:57 PM
Subject: Re: An SQL question...


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

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