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.