SQL double nested query

Question

This query produces the results I want.

Is there a less wordy or more effective way to write this query?

Criteria:
1. Vendor in detail record must have a record in TRANSEFFECTIVE
2. Get the most recent (begin_frame) record from TRANSDETAIL
3. Tells me if the vendor has any other categories in the TRANSMODAL table.

In this case,

Since DDAN has a detail record and has record in TRANSEFFECTIVE, report that DDAN has an AIR,GROUND,and SEA modal record.

Since POKE has a detail record and has record in TRANSEFFECTIVE, report that POKE has only AIR and GROUND modal records.

select vend_code,
       vend_numb,
       description,
       freight_mode
from (
select vend_code,
       vend_numb,
       begin_frame,
       freight_mode,
       freight_count,
       description,
      row_number() over (partition by vend_code,
                                      vend_numb,
                          (case modal_class_code
                           when 'A1' then 1
                          when 'A2' then 1
                           when 'G1' then 2
                           when 'S1' then 3
                          end)
                         order by vend_code, 
                                  vend_numb) as modal_class_count
from 
(
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,
      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
 )
   where freight_choice=1
)
where modal_class_count=1
;

Answer 1

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.