This content has been marked as final. Show 4 replies
If you have multiple columns from the child table, such as the EIT table then you will need to select MAX for all these columns.
Also if you have included some MAX columns and some detail columns from a child table then you may need to remove all the detail columns so that you get one row per person, then add the child columns back with the MAX function to get one row per person.
I am kind of new to discoverer-desktop (about 6 months now).I donot have access to SQL..Can you pl. give me an example or the exact query/function Or steps so i can use at the earliest.
The Person EIT has these things:
Type = Visa details
'Details' section = has multiple rows, and each row has extra info (visa type, visa#, issue_dt, exp_dt)...
so, i need to get only the current row/data for the person.. How do we get that..How do we pick up the latest 'Detail' row..
I tried using the MAX function on Issue_date (which i think is wrong right) from the 'Select Items' section (disco-desktop), from the Person Extra Informat Type folder....but, this does not work.. Then, i thought i need to write a subquery or a new condition.. to somehow pickup only the 'current row'..
pl. let me know..
Can you pl. respond to my earlier question below. If u can get me an example that would be great (condition/calculation/subquery?).. i can use it the same way ..
To get the latest row you have two options. You can use an aggregate function ie. MAX so that you get one row for each person. Then use the MAX function to pick the value from the group of rows that have been aggregated together.
So for the latest visa_type based on the issue_dt you would use a calculation like:
max(visa_type) keep (dense_rank first order by issue_dt)
The other option is to use an analytic calculation to order the rows for a person based on the issue_dt, something like:
row_number() over (partition by person_id order by issue_dt)
Then add a condition where this calculation = 1.