I have two database files that I need to join in an SQL. ARMST will be joined to SYNOT6 which may have no associated records or may have many associated records to ARMST. The two files are joined on company code and customer number. This is what I am doing so far and it is working like I want:
================================================== ============================
select a.accust,
a.acname,
a.aca01,
a.aca02,
a.aca03,
a.aca04,
a.aca06,
a.aca05,
a.acsr,
a.accrcd,
a.accrlm,
coalesce(ics.tomdy(max(b.snedat)), ' '),
coalesce(ics.tomdy(max(b.snfdat)), ' '),
a.acslno,
a.acslnm
from armst a left outer join synot6 b on a.accomp = b.sncomp and a.accust = b.sncust
group by a.accust, a.acname, a.aca01, a.aca02, a.aca03, a.aca04, a.aca06, a.aca05, a.acsr, a.accrcd, a.accrlm, a.acslno, a.acslnm
================================================== ============================
As you can see I'm using a User Defined Function (ics.tomdy) in the secondary file (to return a date from numeric YYYYMMDD to character MM/DD/YY)
This is working like I need it to but I need to add one more field from the secondary file. It is the user id for the date found in b.snedat. I'm getting the latest date there by using MAX. I need to retrieve the user id for the record that has the MAX(b.snedat) in it. Is this possible or do I need to go another route completely?
I'm still learning some of the advanced (as well as NOT advanced) uses of SQL and need some help on this one.
Thanks!!!!!
================================================== ============================
select a.accust,
a.acname,
a.aca01,
a.aca02,
a.aca03,
a.aca04,
a.aca06,
a.aca05,
a.acsr,
a.accrcd,
a.accrlm,
coalesce(ics.tomdy(max(b.snedat)), ' '),
coalesce(ics.tomdy(max(b.snfdat)), ' '),
a.acslno,
a.acslnm
from armst a left outer join synot6 b on a.accomp = b.sncomp and a.accust = b.sncust
group by a.accust, a.acname, a.aca01, a.aca02, a.aca03, a.aca04, a.aca06, a.aca05, a.acsr, a.accrcd, a.accrlm, a.acslno, a.acslnm
================================================== ============================
As you can see I'm using a User Defined Function (ics.tomdy) in the secondary file (to return a date from numeric YYYYMMDD to character MM/DD/YY)
This is working like I need it to but I need to add one more field from the secondary file. It is the user id for the date found in b.snedat. I'm getting the latest date there by using MAX. I need to retrieve the user id for the record that has the MAX(b.snedat) in it. Is this possible or do I need to go another route completely?
I'm still learning some of the advanced (as well as NOT advanced) uses of SQL and need some help on this one.
Thanks!!!!!
Comment