Howdy Experts; I have a basic query that I am using to retrieve some values for a Crystal report (see attached), however I need to somehow change "sysdate" in the case statement to the following:
select max(aging_date) from hsp_bkt_aging_hx
It needs to just be a stand alone value, meaning there is no relationship between the tables in the main query and hsp_bkt_aging_hx.
This must be acceptable in Crystal as a command as well. I'm hoping I'm not seeing something very obvious. Any help would be appreciated.
SELECT SUM(hsp_transactions.tx_amount) "90DREV_NONCAP",
WHEN((sysdate -1) -to_date('01-MAR-10') < 90) THEN(ROUND((sysdate -1) -to_date('01-MAR-10'), 0))
INNER JOIN hsp_account ON hsp_transactions.hsp_account_id = hsp_account.hsp_account_id
LEFT JOIN zc_fin_class ON hsp_transactions.fin_class_c = zc_fin_class.fin_class_c
WHERE hsp_transactions.tx_type_ha_c = '1'
AND hsp_transactions.tx_post_date >= TRUNC(
FROM hsp_bkt_aging_hx) -90)
AND hsp_transactions.tx_post_date <=
AND hsp_account.serv_area_id = 40
AND hsp_account.loc_id = 3010
AND zc_fin_class.title NOT LIKE '%CAP%'