select a.unique_id as agencyID, 'A' AS locSystem, 
 case
      when substr(a.sri,1,1)='I'
      then 'I'
      when substr(a.sri,1,1)='U'
      then 'US'
      when substr(a.sri,1,1)='S'
      then 'SR'
      when substr(a.sri,1,1)='T'
      then 'SR'
      when substr(a.sri,1,1)='N'
      then 'SR'
      when substr(a.sri,1,1)='C'
      then 'O'
      when substr(a.sri,1,1)='R'
      then 'O'
      when substr(a.sri,1,1)='L'
      then 'L'
      when substr(a.sri,1,1)='P'
      then 'X'
      when substr(a.sri,1,1)='F'
      then 'X'
  end as routeType,
  a.SRI as routeName, a.county_name as county, a.mp_start as startOffset, a.mp_end as endOffset, 
  a.unique_id as gisID, a.street as majorRoadName, a.segmentlength as segmentLength,
  a.spat_join_district as district, a.town_name as city,
 case
      when a.legis_class = 'I'
      then '2'
      when a.legis_class = 'II'
      then '2'
      when a.legis_class = 'III'
      then '2'
      when a.legis_class = 'IV'
      then '4'
      when a.legis_class = 'V'
      then '4'
      when a.legis_class = 'VI'
      then '5'
      when a.legis_class = 'VII'
      then '1'
      else '99' End as jurisdiction,  
 case
      When a.funct_class = '1' THEN 'R'
      When a.funct_class = '2' THEN 'R'
      When a.funct_class = '6' THEN 'R'
      When a.funct_class = '7' THEN 'R'
      When a.funct_class = '8' THEN 'R'
      When a.funct_class = '9' THEN 'R'
      When a.funct_class = '11' THEN 'U'
      When a.funct_class = '12' THEN 'U'
      When a.funct_class = '14' THEN 'U'
      When a.funct_class = '16' THEN 'U'
      When a.funct_class = '17' THEN 'U'
      When a.funct_class = '19' THEN 'U' Else 'X' END as areaType,
 case
      When a.funct_class = '1' THEN '1'
      When a.funct_class = '11' THEN '1'
      When a.funct_class = '2' THEN '2'
      When a.funct_class = '12' THEN '2'
      When a.funct_class = '14' THEN '3'
      When a.funct_class = '6' THEN '4'
      When a.funct_class = '7' THEN '5'
      When a.funct_class = '8' THEN '6'
      When a.funct_class = '9' THEN '7'
      When a.funct_class = '19' THEN '7'
      When a.funct_class = '17' THEN '6'
      When a.funct_class = '16' THEN '4' Else '99' END as roadwayClass1,
 case
      When a.no_lanes = '1' THEN '1'
      When a.no_lanes = '2' THEN '1'
      When a.no_lanes = '3' THEN '1'
      When a.no_lanes = '4' THEN '2'
      When a.no_lanes = '5' THEN '2'
      When a.no_lanes = '6' THEN '3'
      When a.no_lanes = '7' THEN '3'
      When a.no_lanes = '8' THEN '4' END AS d1numThruLane,
 case
      When a.no_lanes = '1' THEN '1'
      When a.no_lanes = '2' THEN '1'
      When a.no_lanes = '3' THEN '1'
      When a.no_lanes = '4' THEN '2'
      When a.no_lanes = '5' THEN '2'
      When a.no_lanes = '6' THEN '3'
      When a.no_lanes = '7' THEN '3'
      When a.no_lanes = '8' THEN '4' END AS d2numThruLane,
 a.lane_width as d1avgLaneWidth,
 a.lane_width as d2avgLaneWidth,
 case
      When a.median_type = '1' THEN '1'
      When a.median_type = '2' THEN '4'
      When a.median_type = '3' THEN '9' Else '99' END AS medianType1,
 a.median_width as medianWidth,
  case 
      When a.shoulder_type = '1' THEN '98'
      When a.shoulder_type = '2' THEN '1'
      When a.shoulder_type = '3' THEN '3'
      When a.shoulder_type = '4' THEN '99'
      When a.shoulder_type = '5' THEN '4'
      When a.shoulder_type = '6' THen '5' End as d1shoulderTypeOut,
    case 
      When a.shoulder_type = '1' THEN '98'
      When a.shoulder_type = '2' THEN '1'
      When a.shoulder_type = '3' THEN '3'
      When a.shoulder_type = '4' THEN '99'
      When a.shoulder_type = '5' THEN '4'
      When a.shoulder_type = '6' THen '5' End as d2shoulderTypeOut,
    case 
      When a.shoulder_type = '1' THEN '98'
      When a.shoulder_type = '2' THEN '1'
      When a.shoulder_type = '3' THEN '3'
      When a.shoulder_type = '4' THEN '99'
      When a.shoulder_type = '5' THEN '4'
      When a.shoulder_type = '6' THen '5' End as d1shoulderTypeIn, 
  case 
      When a.shoulder_type = '1' THEN '98'
      When a.shoulder_type = '2' THEN '1'
      When a.shoulder_type = '3' THEN '3'
      When a.shoulder_type = '4' THEN '99'
      When a.shoulder_type = '5' THEN '4'
      When a.shoulder_type = '6' THen '5' End as d2shoulderTypeIn,
  a.shldr_right as d1avgShoulderWidthOut,
  a.shldr_left as d1avgShoulderWidthIn,
  a.shldr_right as d2avgShoulderWidthIn,
  a.shldr_left as d2avgShoulderWidthOut,
  case
      When a.access_control = '1' THEN '1'
      When a.access_control = '2' THEN '2' Else '3' End as accessControl,
  '1' as growthFactor,
  case
      When a.facility_type = '1' THEN '1'
      When a.facility_type = '2' THEN '2' 
      Else '99' END as operationWay,
 a.segmentlength as segmentlength
      
FROM  roads_2009_midyear_6_8_2010 a

WHERE FUNCT_CLASS <> '9'
  AND FUNCT_CLASS <> '19'
  AND FUNCT_CLASS <> '8'
  AND FUNCT_CLASS <> '0'
  AND SRI NOT LIKE 'R%';