SQL & PL/SQL

Ok here is my problem everyone. I have a query that works in principle. What it does is pull data from one query and union it with data from another. I use nulls to match the union's selected columns where that data does not exist in the other query.

I have tried using the second statement as a sub-select in the FROM clause of my first statement but noticed that the UNION of the two queries is much faster (about 10-15 seconds as opposed to 1+ minute(s)).

Now, here is my query:

SELECT dlr_portfolio.*
FROM
(SELECT kpr.attribute1 company_code,
chrb.id contractheaderid,
chrb.contract_number contract_number,
chrb.end_date effective_to,
chrb.start_date effective_from,
chrb.sts_code,
chrb.scs_code,
khr.term_duration term_in_months,
hp.party_name customer_name,
pov.vendor_name dealer_name,
null asset_number,
NVL(kl.residual_value, 0) residual_amount,
tii.serial_number serial_number,
CASE WHEN orb.object1_id1 = 'M' THEN 'Monthly'
WHEN orb.object1_id1 = 'Q' THEN 'Quarterly'
WHEN orb.object1_id1 = 'S' THEN 'Semi-Annual'
WHEN orb.object1_id1 = 'A' THEN 'Annual'
ELSE 'Unknown' END payment_frequency,
null csa_amount,
null rent_amount,
(SELECT MIN(c.stream_element_date)
FROM okl_streams a,
okl_strm_type_tl b,
okl_strm_elements c
WHERE b.name in ('CAT RENT - NO TAX', 'RENT')
AND a.say_code = 'CURR'
AND ((TRUNC(TO_DATE(c.stream_element_date),'MM') >= TRUNC(TO_DATE(sysdate),'MM')
AND orb.object1_id1 IN ('Q','S','A'))
OR (TRUNC(TO_DATE(c.stream_element_date),'MM') = TRUNC(TO_DATE(sysdate),'MM')
AND orb.object1_id1 = 'M'))
AND a.kle_id = klv.id
AND c.stm_id = a.id
AND b.id = a.sty_id) next_payment_date
FROM okl_k_party_roles kpr,
okl_k_headers khr,
okc_k_headers_b chrb,
hz_parties hp,
okc_k_party_roles_v prc,
okc_k_party_roles_v prd,
po_vendors pov,
hz_cust_accounts hca,
okc_k_lines_v klv,
okl_k_lines kl,
okl_txl_itm_insts tii,
okc_rules_b orb,
catokl_k_payment_summary_v psv
WHERE 1 = 1
AND klv.lse_id = 33
AND prd.ROLE = 'Lease Vendor'
AND chrb.scs_code = 'LEASE'
AND chrb.application_id = 540
AND chrb.sts_code IN ('BOOKED', 'EVERGREEN')
AND orb.rule_information_category = 'LASLL'
AND prc.jtot_object1_code = 'OKX_OPERUNIT'
AND prd.jtot_object1_code = 'OKX_VENDOR'
AND psv.slhid = orb.object2_id1
AND psv.contractheaderid = chrb.ID
AND klv.dnz_chr_id = chrb.ID
AND klv.ID = kl.ID
AND tii.dnz_cle_id (+) = klv.id
AND prd.object1_id1 = pov.vendor_id
AND prd.dnz_chr_id = chrb.ID
AND prd.chr_id = chrb.ID
AND chrb.ID = prc.chr_id
AND chrb.ID = khr.ID
AND prc.ID = kpr.ID
AND hp.party_id = hca.party_id
AND hca.cust_account_id = chrb.cust_acct_id
AND hr_security.show_bis_record(chrb.authoring_org_id) = 'TRUE'
AND chrb.id = '44015'
-- Performs the UNION between both main queries --
UNION
-- Performs the UNION between both main queries --
SELECT null company_code,
match.ID contractheaderid,
match.contract_number,
null effective_to,
null effective_from,
null sts_code,
null scs_code,
null term_in_months,
null customer_name,
null dealer_name,
match.asset_number,
null residual_amount,
null serial_number,
null payment_frequency,
MAX (match.csa_payment_amount) csa_amount,
MAX (match.rent_payment_amount) rent_amount,
null next_payment_date
FROM (
SELECT rent.ID,
rent.financialassetlineid,
rent.contract_number,
rent.asset_number,
rent.payment_amount rent_payment_amount,
null csa_payment_amount,
rent.rent_end_date,
NULL csa_end_date
FROM (
SELECT a.CONTRACTHEADERID ID,
a.FINANCIALASSETLINEID,
a.CONTRACT_NUMBER,
a.ASSET_NUMBER,
a.PAYMENT_AMOUNT,
a.PAYMENT_STREAM_TYPE stream_type,
a.PAYMENT_STREAM_TYPE_PURPOSE stream_type_purpose,
CASE WHEN a.PAYMENT_STREAM_TYPE_PURPOSE = 'RENT'
AND TO_DATE(TO_CHAR(a.PAYMENT_START_DATE, 'MON-YYYY'), 'MON-YYYY') <= TO_DATE(TO_CHAR(SYSDATE, 'MON-YYYY'), 'MON-YYYY')
AND TO_DATE(TO_CHAR(a.PAYMENT_END_DATE, 'MON-YYYY'), 'MON-YYYY') >= TO_DATE(TO_CHAR(SYSDATE, 'MON-YYYY'), 'MON-YYYY')
THEN a.PAYMENT_END_DATE ELSE NULL END rent_end_date
FROM catokl_k_payment_v a
ORDER BY a.CONTRACT_NUMBER) rent
WHERE 1=1
AND rent.rent_end_date IS NOT NULL
UNION ALL
SELECT csa.ID,
csa.financialassetlineid,
csa.contract_number,
csa.asset_number,
null rent_payment_amount,
csa.payment_amount csa_payment_amount,
NULL rent_end_date,
csa.csa_end_date
FROM (
SELECT a.CONTRACTHEADERID ID,
case when a.FINANCIALASSETLINEID is null
then multi_asset.financialassetlineid
else a.FINANCIALASSETLINEID end financialassetlineid,
a.CONTRACT_NUMBER,
case when a.asset_number is null
then multi_asset.asset_number
else a.asset_number end asset_number,
a.PAYMENT_AMOUNT,
a.PAYMENT_STREAM_TYPE stream_type,
a.PAYMENT_STREAM_TYPE_PURPOSE stream_type_purpose,
CASE WHEN a.PAYMENT_STREAM_TYPE LIKE '%CSA%'
AND TO_DATE(TO_CHAR(a.PAYMENT_START_DATE, 'MON-YYYY'), 'MON-YYYY') <= TO_DATE(TO_CHAR(SYSDATE, 'MON-YYYY'), 'MON-YYYY')
AND TO_DATE(TO_CHAR(a.PAYMENT_END_DATE, 'MON-YYYY'), 'MON-YYYY') >= TO_DATE(TO_CHAR(SYSDATE, 'MON-YYYY'), 'MON-YYYY')
THEN a.PAYMENT_END_DATE ELSE NULL END csa_end_date
FROM catokl_k_payment_v a,
(select a.contractheaderid
,a.financialassetlineid
,a.asset_number
from catokl_k_payment_v a
where a.PAYMENT_STREAM_TYPE_PURPOSE = 'RENT') multi_asset
where a.CONTRACTHEADERID = multi_asset.contractheaderid
ORDER BY a.CONTRACT_NUMBER) csa
WHERE 1=1
AND csa.csa_end_date IS NOT NULL) match
WHERE match.ID = '44015'
GROUP BY match.ID ,match.financialassetlineid ,match.contract_number ,match.asset_number) dlr_portfolio


---------

My result set looks something like this (very trimmed down for forum purposes):
COMPANY_CODE CONTRACTHEADERID ASSET_NUMBER
560 44015 NULL
NULL 44015 OKL694

You will notice that we have the same contractheaderid (amongst a few other columns such as CONTRACT_NUMBER) that match in both queries.

What I need to do is return OKL694 on the same row as the first line that contains COMPANY_CODE. This works IF I do a MAX(ASSET_NUMBER) and then Group By the other columns in the query.

The probem with that solution is that some may have multiple asset numbers to one contract. If the query should return three assets, it only returns one, the MAX, instead of all three.

Is there anyway to go about merging these two result sets into one line but still be functional enough to handle mult-asset contracts?

Message was edited by:
user516014