The following code will find the number and frequency
of uncoded road names (on, from, or toward).

These road names can  then be searched in TEAAS and the
Uncoded_Road_Search_Combined.sql program to identify
alternate spellings for inclusion into TEAAS.

Written by:	Jeff Jaeger
Revised on:	January 25, 2007

--------------------------------------------------------
Use the following code (suffixes not required):

Note - use % instead of prefixes, spaces, suffixes, etc.
--------------------------------------------------------

DEF NAME = '%C%L%V%R%D%'

DROP TABLE UNCODED_ROADS;

CREATE TABLE UNCODED_ROADS
(SPELLING, COUNT, CODE) AS
SELECT ON_RD_TXT, COUNT(CRSH_ID), 0
FROM MVC_LOCATION
WHERE CRSH_ID > 98999999
AND ON_RD_TXT LIKE '&&NAME'
GROUP BY ON_RD_TXT
UNION
SELECT FRM_RD_TXT, COUNT(CRSH_ID), 0
FROM MVC_LOCATION
WHERE CRSH_ID > 98999999
AND FRM_RD_TXT LIKE '&&NAME'
GROUP BY FRM_RD_TXT
UNION
SELECT TWRD_RD_TXT, COUNT(CRSH_ID), 0
FROM MVC_LOCATION
WHERE CRSH_ID > 98999999
AND TWRD_RD_TXT LIKE '&&NAME'
GROUP BY TWRD_RD_TXT;

DROP TABLE UNCODED_ROADS_2;

CREATE TABLE UNCODED_ROADS_2
(SPELLING, COUNT, CODE) AS
SELECT SPELLING, COUNT, FTR_CD
FROM UNCODED_ROADS, FTV_FEATURE_NAME
WHERE SPELLING = FTR_TXT(+);

DROP TABLE UNCODED_ROADS;

SELECT SPELLING, SUM(COUNT)
FROM UNCODED_ROADS_2
WHERE CODE IS NULL
GROUP BY SPELLING
ORDER BY SPELLING;

DROP TABLE UNCODED_ROADS_2;