Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In the below query how to add the aliases assignedTOiti, assignedTOasstAdm, assignedTOhlp, closedDate to the group by clause.

I am struggling for the past few hours.

SELECT ieor.aper07_req_k                                  AS reqno,
       ford.apeh09_supervisor_cds_d                       AS ll6CdsID,
       'Ford Offboard Req'                                AS reqtype,
       par_val1.aper17_parameter_val_x                    AS requeststatus,
       (SELECT Min(iti2.aper07_req_created_s)
        FROM   iaper07_employee_offbrd_req iti2
        WHERE  ieor.aper07_req_k = iti2.aper07_req_k
               AND iti2.aper06_req_status_k = 4)          AS assignedTOiti,
       (SELECT Min(hlp2.aper07_req_created_s)
        FROM   iaper07_employee_offbrd_req hlp2
        WHERE  ieor.aper07_req_k = hlp2.aper07_req_k
               AND hlp2.aper06_req_status_k = 5)          AS assignedTOasstAdm,
       (SELECT Min(asst2.aper07_req_created_s)
        FROM   iaper07_employee_offbrd_req asst2
        WHERE  ieor.aper07_req_k = asst2.aper07_req_k
               AND asst2.aper06_req_status_k = 12)        AS assignedTOhlp,
       (SELECT Min(clsd2.aper07_req_created_s)
        FROM   iaper07_employee_offbrd_req clsd2
        WHERE  ieor.aper07_req_k = clsd2.aper07_req_k
               AND clsd2.aper06_req_status_k IN ( 3, 9 )) AS closedDate,
       ieor.aper07_assigned_to_cds_d,
       ieor.aper07_comment_x,
       ieor.aper07_last_updt_user_c,
       ieor.aper07_last_updt_s                            AS last,
       ieor.apeh09_employee_k,
       ieor.aper06_req_status_k,
       ipw.apeh04_first_n,
       ipw.apeh04_cds_d,
       ipw.apeh04_last_n,
       ipw.apeh04_person_type_c,
       irs.aper06_req_status_x,
       offr.aper15_offboard_reason_x                      AS offboardReason,
       Min(aper07_req_created_s)                          AS creationDate
FROM   iaper07_employee_offbrd_req ieor,
       iapeh05_person_vw ipw,
       iapeh09_ford_employee ford,
       iaper06_req_status irs,
       iaper15_offbrd_req_reason offr,
       iaper17_parameter_values par_val1
WHERE  ford.apeh09_cds_d = ipw.apeh04_cds_d
       AND ieor.apeh09_employee_k = ipw.apeh04_visitor_k
       AND irs.aper06_req_status_k = ieor.aper06_req_status_k
       AND offr.aper15_offboard_reason_k = ieor.aper15_offboard_reason_k
       AND ieor.aper06_req_status_k = par_val1.aper17_parameter_val_r
       AND ipw.apeh22_region_k = 1
       AND ieor.aper07_last_updt_s = (SELECT Max(aper07_last_updt_s)
                                      FROM   iaper07_employee_offbrd_req b
                                      WHERE  ieor.aper07_req_k = b.aper07_req_k)
       AND ieor.aper07_req_k = 3387
GROUP  BY ieor.aper07_req_k,
          ford.apeh09_supervisor_cds_d,
          ieor.aper07_assigned_to_cds_d,
          ieor.aper07_comment_x,
          ieor.aper07_last_updt_user_c,
          ieor.aper07_last_updt_s,
          ieor.apeh09_employee_k,
          ieor.aper06_req_status_k,
          ipw.apeh04_first_n,
          ipw.apeh04_cds_d,
          ipw.apeh04_last_n,
          ipw.apeh04_person_type_c,
          irs.aper06_req_status_x,
          par_val1.aper17_parameter_val_x,
          offr.aper15_offboard_reason_x
HAVING ipw.apeh04_person_type_c NOT IN( 'c', 'C' )
share|improve this question
    
Please don't cross-post. –  Mat Oct 11 '12 at 7:02
add comment

1 Answer 1

Using the with clause will help keep your queries readable:

with <some_meaningful_name> as (
  select
    <complex_expression1> as <alias1>,
    <complex_expression2> as <alias2>,
    <other_columns>
  from
    ...
)
select
  <some_columns>,
  <aggregate_expression>
from
  some_meaningful_name
group by
  <some_columns>;
share|improve this answer
2  
In standard SQL, the dbms is supposed to act like it evaluates the SELECT clause after the GROUP BY clause. That's why the GROUP BY clause can't see aliases in the SELECT clause--they haven't been evaluated (created) yet. I like common table expressions for reducing complexity like this. –  Mike Sherrill 'Cat Recall' Oct 11 '12 at 9:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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