Retrieving and Expanding List Items in Queries

This Wiki is read-only for reference purposes to avoid broken links.

In many ADempiere tables, reference lists are used to restrict the possible field values to a known set. Decoding these values to the longer list entries can be performed by using the following join in queries:

LEFT OUTER JOIN
(SELECT
	ad_ref_list."name" AS list_value,
	ad_ref_list."value" AS code
FROM
	adempiere.ad_ref_list,
	adempiere.ad_reference
WHERE
	ad_ref_list.ad_reference_id = ad_reference.ad_reference_id AND
	ad_reference."name" = '<My Refernce List>') lv
ON lv.code = <The code to be decoded>

For example, the following query provides a summary of the number and amount of payments that occured on a given day by tender and credit card type

SELECT 	rv.datetrx,
	rltt.list_value AS TenderType,
	CASE rltt.list_value
		WHEN 'Credit Card' THEN rlcc.list_value
		ELSE null
	END AS CardType,
	count(rv.c_payment_id) AS NumTrx,
	sum(rv.payamt) AS Total
FROM
	adempiere.rv_payment rv
	LEFT OUTER JOIN
	(SELECT
		ad_ref_list."name" AS list_value,
		ad_ref_list."value" AS code
	from
		adempiere.ad_ref_list,
		adempiere.ad_reference
	where
		ad_ref_list.ad_reference_id = ad_reference.ad_reference_id AND
		ad_reference."name" = 'C_Payment Tender Type') rltt
	ON rltt.code = rv.tendertype
	LEFT OUTER JOIN
	(SELECT
		ad_ref_list."name" AS list_value,
		ad_ref_list."value" AS code
	from
		adempiere.ad_ref_list,
		adempiere.ad_reference
	where
		ad_ref_list.ad_reference_id = ad_reference.ad_reference_id AND
		ad_reference."name" = 'C_Payment CreditCard Type') rlcc
	ON
		rlcc.code = rv.creditcardtype
WHERE
	rv.ad_client_id = <MyClient> AND
	rv.isactive = 'Y' AND
	rv.isreceipt = 'Y' AND
	rv.datetrx = <MyReportDate>
GROUP BY
	rv.datetrx,
	TenderType,
	CardType
ORDER BY
	rv.datetrx ASC,
	TenderType ASC,
	CardType ASC;