1
\$\begingroup\$

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
;
\$\endgroup\$
2
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.