If you are writing an SQL datasource report and you want to do the equivalent of the part:getCorrespondenceAddress() or party:getBillingAddress() functions then it is not at all obvious how to do it (or it wasn't to me).

Below are two sets of SQL that do the trick. The first is for cases where the customer id is a parameter, the second is for cases where you a dealing with multiple customers. Both rely on ranking the suitability of the contact depending on whether it is a preferred contact and its contact purpose.

If the customer id is known then (assuming you want a Correspondence address):

select e.entity_id as cid, e.name as cname, e.description as cdescription,
   addr.address as address, addr.suburb as suburb, addr.state as state,
   addr.preferred as preferred, addr.purpose as purpose, addr.rank as rank
from entities e
left join (select
        ccd1.value as address, l.name as suburb, ccd3.value as state,
        ccd4.value as preferred, lcl.code as purpose,
        if((ccd4.value = 'true'), if((lcl.code = 'CORRESPONDENCE'), 30, 10), if((lcl.code = 'CORRESPONDENCE'), 20, 0)) as rank
    from
        contacts cc
    left join contact_details ccd1 ON ccd1.contact_id = cc.contact_id
        and ccd1.name = 'address'
    left join contact_details ccd2 ON ccd2.contact_id = cc.contact_id
        and ccd2.name = 'suburb'
    left join lookups l ON l.code = ccd2.value
    left join contact_details ccd3 ON ccd3.contact_id = cc.contact_id
        and ccd3.name = 'state'
    left join contact_details ccd4 ON ccd4.contact_id = cc.contact_id
        and ccd4.name = 'preferred'
    left join contact_classifications ccl ON ccl.contact_id = cc.contact_id
    left join lookups lcl ON lcl.lookup_id = ccl.lookup_id
    where
        cc.party_id = $P{customerId}
            and cc.name = 'Location'
            and cc.active = 1
    order by if((ccd4.value = 'true'), if((lcl.code = 'CORRESPONDENCE'), 30, 10), if((lcl.code = 'CORRESPONDENCE'), 20, 0)) desc , cc.contact_id desc
    limit 1) as addr ON true
where e.entity_id = $P{customerId};

Note that the order by clause in the above is not needed (because of the 'limit 1'), but if you change the limit to say 10, then you can assure yourself of the ranking behaviour.

If you are retrieving multiple customers (assuming you want a Billing address for all customers with names starting with B:

select e.entity_id, e.name, e.description,
ccd1.value as address, l.name as suburb, ccd3.value as state,
         mx.mrank as mrank
from entities e

join contacts cc on cc.party_id = e.entity_id and cc.name = 'Location' and cc.active = 1
    left join contact_details ccd1 ON ccd1.contact_id = cc.contact_id
        and ccd1.name = 'address'
    left join contact_details ccd2 ON ccd2.contact_id = cc.contact_id
        and ccd2.name = 'suburb'
    left join lookups l ON l.code = ccd2.value
    left join contact_details ccd3 ON ccd3.contact_id = cc.contact_id
        and ccd3.name = 'state'
    left join contact_details ccd4 ON ccd4.contact_id = cc.contact_id
        and ccd4.name = 'preferred'
    left join contact_classifications ccl ON ccl.contact_id = cc.contact_id
    left join lookups lcl ON lcl.lookup_id = ccl.lookup_id
inner join (
select e1.entity_id as id,
        max(if((ccd4.value = 'true'), if((lcl.code = 'BILLING'), 30, 10), if((lcl.code = 'BILLING'), 20, 0))) as mrank
from entities e1

join contacts cc on cc.party_id = e1.entity_id and cc.name = 'Location' and cc.active = 1
    left join contact_details ccd4 ON ccd4.contact_id = cc.contact_id
        and ccd4.name = 'preferred'
    left join contact_classifications ccl ON ccl.contact_id = cc.contact_id
    left join lookups lcl ON lcl.lookup_id = ccl.lookup_id

where e1.name like 'b%'
group by e1.entity_id
) mx on e.entity_id = mx.id and mx.mrank = if((ccd4.value = 'true'), if((lcl.code = 'BILLING'), 30, 10), if((lcl.code = 'BILLING'), 20, 0))
where e.name like 'b%'
order by e.name, e.entity_id;

Hope this saves you some time.

Regards, Tim G