I was wondering if any of the Oracle buffs might know of a way to do this:
I'm trying to run a report that (right now) takes a list of vendor commodity codes, loops over each one and then returns the total number of minority vendors, percentage of total vendors to minority vendors, and the weighted avg. of the minority vendor to the percentages for the entire contract.
The problem that I'm encountering is that on records that have a large number of commodity codes, this report can take 30 secs more or less to generate. I think that it would be possible to considerably reduce the rendering time on this by getting rid of the loop, moving the logic into SQL, and using group by to render the data through SQL, rather than in CF. I can't seem to think of the right syntax to access the total vendors and base percentages in the minority vendor query, nor can I seem to be to union the two queries. Also is there a way to access aliased columns in other columns in the Select statement? But I am able to the the Total Vendors to work properly.
This is the output I'm trying to get:
TOTALVENDORS COMM_CODE BASE_PCT numHispVend HispCertPct HispWeightedAvg
46 Comm Code1 0 0 0 0
213 Comm Code2 0 9 0.04 0
Thanks alot!
Jordan
SELECT DISTINCT count(*) as TotalVendors, ecam_commodity_afs2.Comm_Code,
NVL(ecam_trade_summary.BASE_PCT, 0) BASE_PCT
FROM ecam_vend_comm,
list_vend_address_afs2,
list_vend_mstr_afs2,
ecam_commodity_afs2,
ecam_trade_summary
WHERE ecam_vend_comm.COMM_CODE IN (
SELECT
DISTINCT cip.ecam_commodity_afs2.COMM_CODE
FROM
cip.ecam_trade_summary,
cip.ecam_commodity_afs2
WHERE
cip.ecam_trade_summary.SOLICITATION_ID = 30503
AND
cip.ecam_commodity_afs2.COMM_CODE = cip.ecam_trade_summary.COMM_CODE
AND
cip.ecam_trade_summary.BASE_ALT = 'B'
AND
cip.ecam_trade_summary.trade_summary_rev_no = 1
)
AND list_vend_address_afs2.VENDORCODE = ecam_vend_comm.VEND10
AND list_vend_address_afs2.VEND11 = ecam_vend_comm.VEND11
AND list_vend_address_afs2.VENDORCODE = list_vend_mstr_afs2.VEND10
AND ecam_vend_comm.COMM_CODE = ecam_commodity_afs2.COMM_CODE
AND list_vend_address_afs2.loc_code in ('AU','LO','TX')
AND list_vend_address_afs2.companyname not like ' %'
AND ecam_commodity_afs2.COMM_CODE = ecam_trade_Summary.Comm_Code
AND ecam_trade_summary.Solicitation_ID = 30503
GROUP BY ecam_commodity_afs2.Comm_Code, Base_PCT
ORDER BY ecam_commodity_afs2.Comm_Code
SELECT COUNT(*) as numHispCertVend,
(numHispCertVend / TotalVendors) AS HispCertVendPct,
(BASE_PCT * HispCertVendPct) AS HispWeightedAvg,
ecam_commodity_afs2.Comm_Code
FROM ecam_trade_summary,
ecam_commodity_afs2,
ecam_vend_comm,
list_vend_address_afs2,
list_vend_mstr_afs2
WHERE ecam_trade_summary.SOLICITATION_ID = 30503
AND ecam_commodity_afs2.COMM_CODE = ecam_trade_summary.COMM_CODE
AND ecam_trade_summary.BASE_ALT = 'B'
AND ecam_trade_summary.trade_summary_rev_no = 1
AND list_vend_address_afs2.VENDORCODE = ecam_vend_comm.VEND10
AND list_vend_address_afs2.VEND11 = ecam_vend_comm.VEND11
AND list_vend_address_afs2.VENDORCODE =
list_vend_mstr_afs2.VEND10
AND ecam_vend_comm.COMM_CODE = ecam_commodity_afs2.COMM_CODE
AND list_vend_mstr_afs2.ETHNIC_CODE = 'H'
AND list_vend_address_afs2.MINORITY_VENDOR_IND = 'Y'
AND ecam_vend_comm.CERT_FLAG = 'Y'
AND list_vend_address_afs2.loc_code in ('AU','LO','TX')
AND list_vend_address_afs2.companyname not like ' %'
AND list_vend_mstr_afs2.MBE_WBE IN ('MBE','DBE')
AND list_vend_mstr_afs2.UNDER_LIMIT_FLAG = 'Y'
AND ecam_vend_comm.COMM_CODE IN (
SELECT
DISTINCT ecam_commodity_afs2.COMM_CODE
FROM
ecam_trade_summary,
ecam_commodity_afs2
WHERE
ecam_trade_summary.SOLICITATION_ID = 30503
AND
ecam_commodity_afs2.COMM_CODE = ecam_trade_summary.COMM_CODE
AND
ecam_trade_summary.BASE_ALT = 'B'
AND
ecam_trade_summary.trade_summary_rev_no = 1
)
GROUP BY ecam_commodity_afs2.Comm_Code, HispWeightedAvg
ORDER BY ecam_commodity_afs2.COMM_CODE
