   select a.acdyear_number as agencyID, 'A' AS location, 
    case
      when substr(c.sri,1,1)='I'
      then 'I'
      when substr(c.sri,1,1)='U'
      then 'US'
      when substr(c.sri,1,1)='S'
      then 'SR'
      when substr(c.sri,1,1)='T'
      then 'SR'
      when substr(c.sri,1,1)='N'
      then 'SR'
      when substr(c.sri,1,1)='C'
      then 'O'
      when substr(c.sri,1,1)='R'
      then 'O'
      when substr(c.sri,1,1)='L'
      then 'L'
      when substr(c.sri,1,1)='P'
      then 'X'
      when substr(c.sri,1,1)='F'
      then 'X'
  end as routeType,
  c.sri as routeName, 
  c.mp_accident as locOffset,
  to_char(to_date(c.acddate, 'yyddd'), 'yyyy/mm/dd') as accidentDate,
  to_char(to_date(c.acdtime, 'hh24mi'),'hh24:mi') as accidentTime,
  case
      when c.severity='1'
      then 'K'
      when c.severity ='2'
      then 'A'
      when c.severity ='3'
      then 'B'
      when  c.severity ='4'
      then 'C'
      when  c.severity ='6'
      then 'P'
      when c.severity = '5'
      then 'X'
  end as accidentSeverity1,
  (c.totalfatals + c.pedfatals) as numberOfFatalities,
  (c.totalinjury + c.pedinjury) as numberOfInjuries,
  case
      when c.locfirstevent = '01'
      then '2'
      when c.locfirstevent = '02'
      then '3'
      when c.locfirstevent = '03'
      then '1'
      when c.locfirstevent = '04'
      then '4'
      when c.locfirstevent = '05'
      then '1'
      when c.locfirstevent = '06'
      then '1'
      when c.locfirstevent = '07'
      then '9'
      when c.locfirstevent = '08'
      then '9'
      when c.locfirstevent = '09'
      then '1'
      when c.locfirstevent = '10'
      then '1'
      when c.locfirstevent = '98'
      then '9'
      else '99' 
  end as junctionRelationship,
  case
      when c.locfirstevent = '04'
      then '2'
      else '1'
  end as drivewayIndcator,
    case
      when c.lighting = '01'
      then '1'
      when c.lighting = '02'
      then '2'
      when c.lighting = '03'
      then '3'
      when c.lighting = '04'
      then '4'
      when c.lighting = '05'
      then '5'
      when c.lighting = '06'
      then '5'
      when c.lighting = '98'
      then '7'
      else '99'
  end as lightCondition,
   case
      when c.weather = '01'
      then '1'
      when c.weather = '02'
      then '2'
      when c.weather = '03'
      then '4'
      when c.weather = '04'
      then '6'
      when c.weather = '05'
      then '5'
      when c.weather = '06'
      then '3'
      when c.weather = '07'
      then '9'
      when c.weather = '08'
      then '8'
      when c.weather = '09'
      then '3'
      when c.weather = '10'
      then '3'
      when c.weather = '11'
      then '1'
      else '99'
  end as weatherCondition,
   case
      when c.surfacecondition = '01'
      then '1'
      when c.surfacecondition = '02'
      then '2'
      when c.surfacecondition = '03'
      then '3'
      when c.surfacecondition = '04'
      then '5'
      when c.surfacecondition = '05'
      then '8'
      when c.surfacecondition = '06'
      then '10'
      when c.surfacecondition = '07'
      then '7'
      when c.surfacecondition = '98'
      then '10'
      else '99'
   end as surfaceCondition,
    case
      when c.acdtype = '03' 
      then '1'
      when c.acdtype = '04'
      then '2'
      when c.acdtype = '05'
      then '3'
      when c.acdtype = '06'
      then '4'
      when c.acdtype = '07'
      then '5'
      when c.acdtype = '10'
      then '6'
      when c.acdtype = '09'
      then '7'
      when c.acdtype = '08'
      then '8'
      when c.acdtype = '18'
      then '8'
      when c.acdtype = '19'
      then '8'
      when c.acdtype = '11'
      then '9'
      when c.acdtype = '13'
      then '10'
      when c.acdtype = '16'
      then '10'
      when c.acdtype = '12'
      then '11'
      when c.acdtype = '14'
      then '11'
      when c.acdtype = '15'
      then '11'
      when c.acdtype = '98'
      then '11'
      when c.diagramcode = '01'
      then '21'
      when c.diagramcode = '07'
      then '22'
      when c.diagramcode = '03'
      then '24'
      when c.diagramcode = '04'
      then '24'
      when c.diagramcode = '05'
      then '24'
      when c.diagramcode = '06'
      then '24'
      when c.diagramcode = '02'
      then '25'
      when c.diagramcode = '08'
      then '26'
      when c.acdtype = '01' and c.diagramcode = '98'
      then '27'
      else '99'
    end as collisionType,
  case
      when c.weather = '03' or c.weather = '04' or c.weather = '05' or c.weather = '06' or
      c.weather = '07' or c.weather = '08' or c.weather = '09' or c.weather = '10' or b.vision_blockage = '04' or
      b.vision_blockage = '11'
      Then '2'
      when c.acdtype = '07'
      Then '5'
      when b.vision_blockage = '10'      
      Then '4'
      when b.vision_blockage = '02' or b.vision_blockage = '03'or b.vision_blockage = '05' or b.vision_blockage = '06' or
      b.vision_blockage = '07' or b.vision_blockage = '08' or b.vision_blockage = '09' or b.vision_blockage = '12' or
      b.vision_blockage = '13'
      then '3'
      when b.vision_blockage = '14'
      then '1'
      when b.vision_blockage = '98'
      then '6'
      else '99'
    end as environmentCondition,
    case
      when c.roadcondition = '01'
      then '1'
      when c.surfacecondition = '02' or c.surfacecondition = '03' or c.surfacecondition = '04' or surfacecondition = '05' or
      c.surfacecondition = '07'
      then '2'
      when c.surfacecondition = '06'
      then '3'
      when c.roadcondition = '02'
      then '4'
      when c.roadcondition = '03'
      then '6'
      when c.roadcondition = '04'
      then '9'
      when c.roadcondition = '05'
      then '9'
      else '99'
    end as roadCondition,
    '99' AS schoolbus,
    case 
      when c.adlroadfeatures = '01' or c.adlroadfeatures ='02'
      then 'Y'
      else 'N'
    end as workZone,
    to_number (c.numvehicles) as numVehicles,
    case
      when b.driver_condition = '01'
      then '4'
      when b.driver_condition = '02'
      then '2'
      when b.driver_condition = '03'
      then '3'
      else '1'
    end as drugInvolved,
    'X' AS towIndicator,
    Case
      when c.acdtype = '10' or c.locfirstevent = '05' or c.locfirstevent ='06' 
      then 'Y'
      else 'N' END as runoffIndicator,
    Case
      when c.acdtype = '06'
      then 'Y'
      else 'N' END as pedestrianIndicator,
    Case
      when c.acdtype = '05'
      then 'Y'
      else 'N' END as bikeIndicator,
    'X' AS sideOfDividedHighway,
            
    b.unit_nbr as Vehicle_Number, b.unit_type as Vehicle_Type, 
    b.driver_condition, b.vehicle_action, b.vehicle_type, 
    b.operator_dob, b. arithmetic_operator_dob, 
    c.acdyear as Year, c.severity, c.numvehicles,  
    c.totalfatals, c.totalinjury,
    c.pedfatals, c.pedinjury, c.weather, c.county as County
    
        
  	
  FROM  acdi_persons_loc a
  inner join acdu_vehicles_loc b
	ON (a.acdyear_number = b.acdyear_number
     and a.occupiedcode = b.unit_nbr)
  inner join acdl_crash_loc c
  on (c.acdyear_number = b.acdyear_number)
     where a.positioncode='01'
 ;

 