Maximum values to "display" but all records required 1

[piovesan]

Hi! I have a report where I want to display all properties sold within a certain time frame, and where the data is available, I want to display the purchaser's name. The purchaser name is in a table called PR_CLIENT and there are three fields in that table that I am concerned with......:
Client_ID - which is the unique key (Number)
ClientName - which is a text field
ClientCode - which is a one character text field that is either "C", "V" or "P". For each land parcel, there can be none, or many of any of these. In this report, I only want to DISPLAY the "P" attribute related to the property, and only the "P" attribute with the maximum Client ID (that would represent the latest purchaser.

Another technician created a command for me for this report that reads:
select lnd_hrm_parcel_multi.asset_id, lnd_hrm_parcel_multi.asset_type,
lnd_hrm_parcel_multi.disp_cost, lnd_hrm_parcel_multi.disp_date,
lnd_hrm_parcel_multi.disp_type, lnd_hrm_parcel_multi.disp_value, lnd_hrm_parcel_multi.main_class,
lnd_hrm_parcel_multi.pid, pid_dist.dist_id, lnd_civic_address.civ_id, lnd_civic_address.civ_num,
lnd_civic_address.gsa_name, lnd_civic_address.str_name, lnd_civic_address.str_type,
lnd_pr_client.client_id, lnd_pr_client.clientcode, lnd_pr_client.clientname, lnd_pr_client.clienttype
from sdeadm.lnd_hrm_parcel_multi, sdeadm.pid_dist, sdeadm.lnd_civic_address, sdeadm.lnd_pr_client
where
lnd_hrm_parcel_multi.pid = pid_dist.pid(+)
and lnd_hrm_parcel_multi.pid = lnd_civic_address.pid(+)
and (lnd_hrm_parcel_multi.asset_id = lnd_pr_client.asset_id OR
lnd_hrm_parcel_multi.pid = lnd_pr_client.pid)

Attached is a pdf of what the report looks like currently (I am just playing at the moment). But you can see that the last record, Asset ID 15034 has 4 related records in the Client table. Two are purchasers (P). In the blue section, I would like to see the purchaser in this case be client id 1087 because it is the latest purchaser (Client Code = P and the maximum Client ID of those P records.)

I have also attached (or will try to either in this thread or another) some screen shots showing that I have created a couple of formula fields attempting to A). "select" the maximum Client ID (you can see I am getting the maximum Client ID of all the clients, not the maximum of the "P" clients like I want!!) and B). Display only the "P" purchaser

Anyone able to help? Thanks!

 

[lbass]

Create a formula like this:

//{@Pclient}:
if {command.clientcode} = "P" then
{command.clientID}

Then go to report->selection formula->GROUP and enter:

{@Pclient} = maximum({@Pclient},{command.asset_ID}

-LB

 

[piovesan]

not sure if I should post this on a new thread or not, but it's related to the same report..... The suggestion lbass gave me worked for what I wanted! Thanks!..... but I have found that having the command formulate my joins in the WHERE clause may be the cause of some records not showing up that I expect.... I have read that the joins should be done in a JOIN clause, so I have attempted to do this, however I am not sure of the proper context to create a join between two tables between two different fields.
For example, I have changed the command to read:
SELECT DISTINCT "LND_HRM_PARCEL_MULTI"."ASSET_TYPE", "LND_CIVIC_ADDRESS"."CIV_ID", "LND_CIVIC_ADDRESS"."CIV_NUM", "LND_CIVIC_ADDRESS"."STR_NAME", "LND_CIVIC_ADDRESS"."STR_TYPE", "LND_CIVIC_ADDRESS"."GSA_NAME", "LND_HRM_PARCEL_MULTI"."ASSET_ID", "PID_DIST"."DIST_ID", "LND_HRM_PARCEL_MULTI"."HECTARES", "LND_HRM_PARCEL_MULTI"."PID", "LND_HRM_PARCEL_MULTI"."DISP_DATE", "LND_HRM_PARCEL_MULTI"."DISP_TYPE", "LND_HRM_PARCEL_MULTI"."DISP_COST", "LND_HRM_PARCEL_MULTI"."DISP_VALUE", "LND_HRM_PARCEL_MULTI"."MAIN_CLASS", "LND_PR_REALESTATE_COMMENTS"."RE_LOCATE", "LND_PR_CLIENT"."CLIENTNAME", "LND_PR_CLIENT"."CLIENTCODE"
FROM ((("bla bla bla.... bunch of tables....)....
LEFT OUTER JOIN "SDEADM"."LND_PR_CLIENT" "LND_PR_CLIENT" ON "LND_HRM_PARCEL_MULTI"."PID"="LND_PR_CLIENT"."PID"

But I also want a left outer join between the parcel table and the client table to be between the Asset_ID as well as the PID.......... I have tried:
FROM ((("bla bla bla.... bunch of tables....)....
LEFT OUTER JOIN "SDEADM"."LND_PR_CLIENT" "LND_PR_CLIENT" ON "LND_HRM_PARCEL_MULTI"."PID"="LND_PR_CLIENT"."PID"
OR
LEFT OUTER JOIN "SDEADM"."LND_PR_CLIENT" "LND_PR_CLIENT" ON "LND_HRM_PARCEL_MULTI"."ASSET_ID"="LND_PR_CLIENT"."ASSET_ID"
but I think my OR or maybe it's the brackets () that I have incorrect.... can you help?
Thanks!

 

[lbass]

I hope your 'bla bla bla bunch of tables' are joined together somehow.

LEFT OUTER JOIN "SDEADM"."LND_PR_CLIENT" "LND_PR_CLIENT"
ON "LND_HRM_PARCEL_MULTI"."PID"="LND_PR_CLIENT"."PID" and
"LND_HRM_PARCEL_MULTI"."ASSET_ID"="LND_PR_CLIENT"."ASSET_ID"

-LB