We have another winner

This one is worse than the last one. It's hard to believe this is a single SQL statement.

SELECT from_lane.lane_id, from_lane.lane_type from_lane_type_id,
from_lane.city from_lane_city, from_lane.state_cd from_lane_state_cd,
from_lane.country_cd from_lane_country_cd,
from_lane.low_zip from_lane_low_zip,
from_lane.high_zip from_lane_high_zip, from_lane.other from_lane_other,
to_lane.lane_type to_lane_type_id, to_lane.city to_lane_city,
to_lane.state_cd to_lane_state_cd, to_lane.country_cd to_lane_country_cd,
to_lane.low_zip to_lane_low_zip,
to_lane.high_zip to_lane_high_zip, to_lane.other to_lane_other
FROM (SELECT ID lane_id, from_key_num lane_type, NULL city,
from_low_key_value state_cd, null country_cd, NULL low_zip, NULL high_zip,
NULL other
FROM tms_lane_pre
WHERE from_key_num = 2
UNION
SELECT ID lane_id, from_key_num lane_type, NULL city,
CASE
WHEN c1.min_state = c1.max_state
AND c1.min_state = c2.min_state
AND c1.min_state = c2.max_state
THEN c1.min_state
ELSE NULL
END AS state_cd,
CASE
WHEN c1.min_country = c1.max_country
AND c1.min_country = c2.min_country
AND c1.min_country = c2.max_country
THEN c1.min_country
ELSE NULL
END AS country_cd,
from_low_key_value || '00' low_zip,
from_high_key_value || '99' high_zip, NULL other
FROM tms_lane_pre p
LEFT OUTER JOIN
(SELECT SUBSTR (zip, 1, 3) zip3, MIN (state) min_state,
MAX (state) max_state, MIN(country) min_country,
max(country) max_country
FROM tms_centroid_dtl_pre
GROUP BY SUBSTR (zip, 1, 3)) c1 ON p.from_low_key_value =
c1.zip3
LEFT OUTER JOIN
(SELECT SUBSTR (zip, 1, 3) zip3, MIN (state) min_state,
MAX (state) max_state,MIN(country) min_country,
max(country) max_country
FROM tms_centroid_dtl_pre
GROUP BY SUBSTR (zip, 1, 3)) c2
ON p.from_high_key_value = c2.zip3
WHERE from_key_num = 3
UNION
SELECT ID lane_id, from_key_num lane_type, from_low_key_value city,
SUBSTR (from_high_key_value, 1, 4) state_cd, country,
MIN (zip) low_zip, MAX (zip) high_zip, NULL other
FROM tms_lane_pre p LEFT OUTER JOIN tms_centroid_dtl_pre c
ON p.from_low_key_value = c.city
AND p.from_high_key_value = c.state
WHERE p.from_key_num = 4
GROUP BY ID, from_key_num, from_low_key_value, from_high_key_value, country
UNION
SELECT ID lane_id, from_key_num lane_type,
CASE
WHEN c1.min_city = c1.max_city
AND c1.min_city = c2.min_city
AND c1.min_city = c2.max_city
THEN c1.min_city
ELSE NULL
END city,
CASE
WHEN c1.min_state = c1.max_state
AND c1.min_state = c2.min_state
AND c1.min_state = c2.max_state
THEN c1.min_state
ELSE NULL
END state_cd,
CASE
WHEN c1.min_country = c1.max_country
AND c1.min_country = c2.min_country
AND c1.min_country = c2.max_country
THEN c1.min_country
ELSE NULL
END country_cd,
from_low_key_value low_zip, from_high_key_value high_zip,
NULL other
FROM tms_lane_pre p
LEFT OUTER JOIN
(SELECT zip, MIN (state) min_state, MAX (state) max_state,
MIN (country) min_country, MAX (country) max_country,
MIN (city) min_city, MAX (city) max_city
FROM tms_centroid_dtl_pre
GROUP BY zip) c1 ON p.from_low_key_value = c1.zip
LEFT OUTER JOIN
(SELECT zip, MIN (state) min_state, MAX (state) max_state,
MIN (country) min_country, MAX (country) max_country,
MIN (city) min_city, MAX (city) max_city
FROM tms_centroid_dtl_pre
GROUP BY zip) c2 ON p.from_high_key_value = c2.zip
WHERE from_key_num = 5
UNION
SELECT ID lane_id, from_key_num lane_type, NULL city, NULL state, null country,
from_low_key_value low_zip, from_high_key_value high_zip,
NULL other
FROM tms_lane_pre
WHERE from_key_num = 6
UNION
SELECT ID lane_id, from_key_num lane_type, NULL city, NULL state, null country,
NULL low_zip, NULL high_zip, from_low_key_value other
FROM tms_lane_pre
WHERE from_key_num = 7) from_lane,
(SELECT ID lane_id, to_key_num lane_type, NULL city,
to_low_key_value state_cd, null country_cd, NULL low_zip, NULL high_zip,
NULL other
FROM tms_lane_pre
WHERE to_key_num = 2
UNION
SELECT ID lane_id, to_key_num lane_type, NULL city,
CASE
WHEN c1.min_state = c1.max_state
AND c1.min_state = c2.min_state
AND c1.min_state = c2.max_state
THEN c1.min_state
ELSE NULL
END AS state_cd,
CASE
WHEN c1.min_country = c1.max_country
AND c1.min_country = c2.min_country
AND c1.min_country = c2.max_country
THEN c1.min_country
ELSE NULL
END AS country_cd,
to_low_key_value || '00' low_zip,
to_high_key_value || '99' high_zip, NULL other
FROM tms_lane_pre p
LEFT OUTER JOIN
(SELECT SUBSTR (zip, 1, 3) zip3, MIN (state) min_state,
MAX (state) max_state, MIN (country) min_country,
MAX (country) max_country
FROM tms_centroid_dtl_pre
GROUP BY SUBSTR (zip, 1, 3)) c1 ON p.to_low_key_value =
c1.zip3
LEFT OUTER JOIN
(SELECT SUBSTR (zip, 1, 3) zip3, MIN (state) min_state,
MAX (state) max_state,MIN (country) min_country,
MAX (country) max_country
FROM tms_centroid_dtl_pre
GROUP BY SUBSTR (zip, 1, 3)) c2 ON p.to_high_key_value =
c2.zip3
WHERE to_key_num = 3
UNION
SELECT ID lane_id, to_key_num lane_type, to_low_key_value city,
SUBSTR (to_high_key_value, 1, 4) state_cd,country country_cd, MIN (zip) low_zip,
MAX (zip) high_zip, NULL other
FROM tms_lane_pre p LEFT OUTER JOIN tms_centroid_dtl_pre c
ON p.to_low_key_value = c.city
AND p.to_high_key_value = c.state
WHERE p.to_key_num = 4
GROUP BY ID, to_key_num, to_low_key_value, to_high_key_value, country
UNION
SELECT ID lane_id, to_key_num lane_type,
CASE
WHEN c1.min_city = c1.max_city
AND c1.min_city = c2.min_city
AND c1.min_city = c2.max_city
THEN c1.min_city
ELSE NULL
END city,
CASE
WHEN c1.min_state = c1.max_state
AND c1.min_state = c2.min_state
AND c1.min_state = c2.max_state
THEN c1.min_state
ELSE NULL
END state_cd,
CASE
WHEN c1.min_country = c1.max_country
AND c1.min_country = c2.min_country
AND c1.min_country = c2.max_country
THEN c1.min_country
ELSE NULL
END country_cd,
to_low_key_value low_zip, to_high_key_value high_zip,
NULL other
FROM tms_lane_pre p
LEFT OUTER JOIN
(SELECT zip, MIN (state) min_state, MAX (state) max_state,
MIN (country) min_country, MAX (country) max_country,
MIN (city) min_city, MAX (city) max_city
FROM tms_centroid_dtl_pre
GROUP BY zip) c1 ON p.to_low_key_value = c1.zip
LEFT OUTER JOIN
(SELECT zip, MIN (state) min_state, MAX (state) max_state,
MIN (country) min_country, MAX (country) max_country,
MIN (city) min_city, MAX (city) max_city
FROM tms_centroid_dtl_pre
GROUP BY zip) c2 ON p.to_high_key_value = c2.zip
WHERE to_key_num = 5
UNION
SELECT ID lane_id, to_key_num lane_type, NULL city, NULL state, null country,
to_low_key_value low_zip, to_high_key_value high_zip,
NULL other
FROM tms_lane_pre
WHERE to_key_num = 6
UNION
SELECT ID lane_id, to_key_num lane_type, NULL city, NULL state, null country,
NULL low_zip, NULL high_zip, to_low_key_value other
FROM tms_lane_pre
WHERE to_key_num = 7) to_lane
WHERE from_lane.lane_id = to_lane.lane_id