*/ Revised 10-17-07 to show frm_rd_typ due to problems with routes coded with typ "connector" */ set wrap off set doc off set feedback OFF set verify off set echo OFF set linesize 500 set pagesize 20000 COLUMN mp FORMAT a3 COLUMN frm_rd FORMAT a30 COLUMN twrd_rd FORMAT a25 COLUMN city FORMAT a10 COLUMN sev FORMAT a3 Accept cntynbr NUMBER PROMPT ' Enter the county number: ' Accept rte_id NUMBER PROMPT ' Enter route number: ' --Accept opt number PROMPT ' Enter 1 to include expanded report: ' delete from rd_crsh; commit; insert into rd_crsh (crsh_id, mp, frm_rd, twrd_rd, city, sev) (SELECT l.crsh_id, DECODE (mlpst_nbr, 999.999, 'not', 'MP') mp, NVL(frm_rd.ftr_txt,frm_rd_cls||' '||frm_rd_txt||' '||frm_rd_typ||'(NC)') frm_rd, NVL(twrd_rd.ftr_txt, twrd_rd_cls||' '||twrd_rd_txt||' (NC)') twrd_rd, city_adr city, DECODE(svrty_cd, 1, 'FTL', 2, 'A', 3, 'B', 4, 'C', 'PDO') sev From mvc_location l, mvc_crash_report cr, ftv_feature_name frm_rd, ftv_feature_name twrd_rd, mvc_city_population city Where l.crsh_id >= 99000000 --AND l.crsh_id < 100010000 AND cr.crsh_id >= 99000000 --AND cr.crsh_id < 100010000 AND l.crsh_id = cr.crsh_id AND l.frm_rd_cd = frm_rd.ftr_cd(+) AND l.twrd_rd_cd = twrd_rd.ftr_cd(+) AND l.city_cd = city.city_cd(+) AND l.cnty_nbr = &&cntyNbr AND l.cnty_nbr = NVL(city.cnty_nbr,l.cnty_nbr) AND rd_on_cd = &&rte_id AND (frm_rd.ftr_txt IS NULL OR frm_rd.ftr_prfrd_txt_ind = 'Y') AND (twrd_rd.ftr_txt IS NULL OR twrd_rd.ftr_prfrd_txt_ind = 'Y') AND on_rd_cls <> 'PP' AND on_rd_cls <> 'PVA' AND frm_rd_cls <> 'PP' AND frm_rd_cls <> 'PVA'); commit; insert into rd_crsh (crsh_id, mp, frm_rd, twrd_rd, city, sev) (SELECT o.ordnnc_nbr, DECODE (o.bgn_mlpst_nbr, 999.999, 'not', 'MP') mp, NVL(frm_rd.ftr_txt,o.bgn_rfrnc_id||' (NC)') frm_rd, 'NO CRASH - Ordinance' twrd_rd, 'N/A' city, 'N/A' sev From ftv_ordinance o, ftv_ordinance_audit a, ftv_feature_name frm_rd Where o.ordnnc_nbr = a.ordnnc_nbr AND o.bgn_rfrnc_id = frm_rd.ftr_cd(+) AND o.cnty_nbr = &&cntyNbr AND o.rte_on_id = &&rte_id AND (frm_rd.ftr_txt IS NULL OR frm_rd.ftr_prfrd_txt_ind = 'Y') AND o.rpl_dt is NULL); commit; insert into rd_crsh (crsh_id, mp, frm_rd, twrd_rd, city, sev) (SELECT o.ordnnc_nbr, DECODE (o.end_mlpst_nbr, 999.999, 'not', 'MP') mp, NVL(frm_rd.ftr_txt,o.end_rfrnc_id||' (NC)') frm_rd, 'NO CRASH - Ordinance' twrd_rd, 'N/A' city, 'N/A' sev From ftv_ordinance o, ftv_ordinance_audit a, ftv_feature_name frm_rd Where o.ordnnc_nbr = a.ordnnc_nbr AND o.end_rfrnc_id = frm_rd.ftr_cd(+) AND o.cnty_nbr = &&cntyNbr AND o.rte_on_id = &&rte_id AND (frm_rd.ftr_txt IS NULL OR frm_rd.ftr_prfrd_txt_ind = 'Y') AND o.rpl_dt is NULL); commit; prompt -- print out heading info, for clearer report generation SET HEADING OFF SELECT 'Mileposting report for: '|| (SELECT FTR_TXT FROM FTV_FEATURE_NAME WHERE FTR_CD = &&rte_id AND FTR_PRFRD_TXT_IND = 'Y') || ' ('|| &&rte_id ||'), ' || (SELECT CNTY_NM FROM MVC_COUNTY C WHERE CNTY_NBR = &&cntynbr) || ' COUNTY (' || &&cntynbr || ')' FROM DUAL; SELECT 'Ran On: '|| to_Char(SYSDATE,'MM/DD/YY, HH:MIAM') || ' Ran By: '||(SELECT USERNAME FROM USER_USERS) FROM DUAL; SET HEADING ON; prompt COLUMN mp_prct HEADING "MP'd|%" FORMAT a4 COLUMN MP_crsh HEADING "MP'd|Crashes" FORMAT 99999 COLUMN tot_crsh HEADING "Total|Crashes" FORMAT 99999 Select (Select COUNT(crsh_id) From rd_crsh Where mp = 'MP') MP_crsh, (Select COUNT(crsh_id) From rd_crsh) tot_crsh, DECODE((Select COUNT(crsh_id) From rd_crsh), 0, 'N/A', (Select COUNT(crsh_id) From rd_crsh Where mp = 'MP')/ (Select COUNT(crsh_id) From rd_crsh)*100) mp_prct From dual; /* show a listing of all the unmileposted crashes grouped by From road */ Select COUNT(crsh_id) "#CRASHES", frm_rd From rd_crsh WHERE mp = 'not' Group by frm_rd Order by COUNT(crsh_id) desc; /* show a listing of all the unmileposted crashes that includes basic location information */ Select crsh_id "crsh_ID", frm_rd, twrd_rd, city, sev From rd_crsh Where (crsh_id||1 = crsh_id||1 OR crsh_id||'y' = crsh_id||1) AND mp = 'not' Order by frm_rd, twrd_rd, crsh_id; prompt prompt --delete from rd_crsh; --commit;