I'm needing to duplicate the use of the Oracle MAX() function in my Cognos Query data sets.
We have two tables, EMP and ELECTIONS, where EMP contains the demographic data for a person, and ELECTIONS contains thier benefit elections over time (so, there could be several rows where the person elected MEDICAL benefits, but one might be "employee-only", the next "employee plus spouse", etc.. Each row would have a date-stamp of when the election was made).
There is always a need to join the EMP and ELECTIONS tables to pull the employees and thier latest set of benefit elections. In PL-SQL, it's easy, gouping on the user id and the benefit, and selecting the MAX(elec_eff_date).
But, how do I duplicate this in Query?
We have two tables, EMP and ELECTIONS, where EMP contains the demographic data for a person, and ELECTIONS contains thier benefit elections over time (so, there could be several rows where the person elected MEDICAL benefits, but one might be "employee-only", the next "employee plus spouse", etc.. Each row would have a date-stamp of when the election was made).
There is always a need to join the EMP and ELECTIONS tables to pull the employees and thier latest set of benefit elections. In PL-SQL, it's easy, gouping on the user id and the benefit, and selecting the MAX(elec_eff_date).
But, how do I duplicate this in Query?