Patti, Your IDJulianHVID is not really a computed column, by the R:BASE definition of a computed column, but is probably an "aggregate" column, based on the SQL standard definition of aggregate functions. I'm guessing your view is something like this:
CREATE VIEW HVLatestDate (ClientID, IDJulianHVID) + AS SELECT ClientID, MAX (HVDate) + FROM HVDetail + GROUP BY ClientID Is your goal to see the detail for the client but just for the most recent Home Visit date? Your query or view to see the detail for the latest visit would be a compound query. Your "group by" view can only show you the latest date per client, and because it is "aggregating" by client, it cannot also show you all the details for that visit. To see the details within the same query, create another view that references your view and your detail, linking the two by the date and the clientID. This view will contain all the details that are in your base table, but only for the row that matches the latest date in your HVLatestDate View. SELECT d1.* FROM HVdetails d1, HVLatestDate l2 + WHERE l2.ClientID = d1.ClientID + AND l2.IDJulianHVID = d1.HVDate If you make this into a view containing a view, it will have all the same column names as the underlying HVdetails table, but only for the latest date. CREATE VIEW HVDetailsLatestOnly AS + SELECT d1.* FROM HVdetails d1, HVLatestDate l2 WHERE l2.ClientID = d1.ClientID AND l2.IDJulianHVID = d1.HVDate Then you should be able to query that view from an R> prompt, or base a report on it. Bill On Thu, Jan 28, 2016 at 2:27 PM, Dan Goldberg <[email protected]> wrote: > Make sure you give it an name(alias). > > > > Create view viewname (ClientID, MxIDJulianHVID) as select Clientid, > max(IDJulianHVID) from tablename group by ClientID > > > > Dan Goldberg > > > > *From:* [email protected] [mailto:[email protected]] *On Behalf Of *Patti > Jakusz > *Sent:* Thursday, January 28, 2016 11:11 AM > *To:* Dan Goldberg > *Subject:* [RBASE-L] - Question about views > > > > Hi Rbase Pros, > > > > I'm trying to learn how to use views. They didn't work very well for me > in 1993, so I ignored them until now. > > > > I have a table of Home Visits for our clients. I created a view > (HVLatestDate) to capture the most recent visit for each client. So I have > 2 columns in my view: > > > > Client ID (I group on this) > > IDJulianHVID (I use the Max function) This is a computed field that > includes the client, visit date and visit type one field. > > > > This part works ok, but if I want to select that computed field from the > view, it won't let me. > > > > I've tried using the name as it appears above (select IDJulianHVID from > HVLatestDate). > > I tried calling it T1.IDJulianHVID and I've tried calling it > (MAX(T1.IDJulianHVID)) and (MAX(IDJulianHVID)). > > Nothing seems to work. I can select the ClientID field though. But I > don't need that one, I need the one I MAXed. > > > > I tried the whole view over again using an uncomputed column (Julian) > instead of the computed column and it still won't let me select it from the > view at the R> prompt. > > > > How can I select this column from this view? > > > > Thanks, > > Patti > > > > > > >
