SQL double nested query
Well, I think I broke SQLFiddle...  Just for clarity's sake, I'd refactor the subqueries... that's the best way I know to do step-wise development and refinement.  I know my subquery names suck - they should be descriptive.  I would also strip out un-necessary column names -- or in my case, comment them out.

with qry1 as
    (
        select td.modal_vend_code as vend_code,
               td.modal_vend_numb as vend_numb,
               td.modal_begin_frame as begin_frame,
               td.modal_description as description,
               te.modal_vend_begin_frame as te_begin,
               te.modal_vend_close_frame as te_close,
               tm.modal_class_code       as modal_class_code,
               case tm.modal_class_code
                   when 'A1' then 'AIRFREIGHT'
                   when 'A2' then 'AIRFREIGHT'
                   when 'G1' then 'GROUND'
                   when 'S1' then 'SEABOUND'
                   else 'UNKNOWN'
                end as freight_mode,
               case tm.modal_class_code
                   when 'A1' then 1
                   when 'A2' then 1
                   when 'G1' then 2
                   when 'S1' then 3
                   else NULL
                end as modal_class,
              rank() over (partition by td.modal_vend_code,
                                        td.modal_vend_numb
                               order by td.modal_begin_frame desc) as freight_choice,
               count(*) over (partition by td.modal_vend_code,
                                           td.modal_vend_numb) freight_count
        from  transdetail td
        inner join transeffective te on td.modal_vend_code = te.modal_vend_code
                                     and td.modal_vend_numb = te.modal_vend_numb
        inner join transmodal  tm  on td.modal_vend_code = tm.modal_vend_code
                                  and td.modal_vend_numb = tm.modal_vend_numb
     )
    ,
    qry2 as
    (
        select vend_code,
               vend_numb,
               -- begin_frame,
               freight_mode,
               -- freight_count,
               description,
              row_number() over (partition by vend_code,
                                              vend_numb,
                                              modal_class
                                 order by vend_code, 
                                          vend_numb) as modal_class_count
        from 
         qry1
           where freight_choice=1
    )

    select vend_code,
           vend_numb,
           description,
           freight_mode
    from qry2
    where modal_class_count=1
    ;


Then again, seeing as when I tried to run this in SQLFiddle, it didn't return for over a minute, and when I tried to refresh, I got a 404 -- my attempt may be a problem.