SQL query, is GROUP BY needed? how to avoid unnecessarily complicated syntax

Question

I am building a query that will build one, pretty large data set based on 4 tables. Here is my query so far:

SELECT  
CLIENT_CA."Requisition_ID",  
CLIENT_REQS."Requisition Title",  
CLIENT_REQS."Country",  
CLIENT_CD."Application_Status",  
CLIENT_CA."Candidate",  
CLIENT_CD."Gender",  
max(case when 
CLIENT_CA."Process_of_Activity" = 'Application Entry' then 
CLIENT_CA."Completed_on" end) as "ENTRY",  
max(case when 
CLIENT_CA."Process_of_Activity" = 'Candidate Selection' then 
CLIENT_CA."Completed_on" end) as "Selection",  
max(case when 
CLIENT_CA."Process_of_Activity" = 'Job Offer' then 
CLIENT_CA."Completed_on" end) as "Offer",  
CLIENT_RR."Status_Reason(E-Rec)"   
FROM CLIENT_CA  
LEFT JOIN CLIENT_CD ON (CLIENT_CA."Candidate_ID"=CLIENT_CD."Candidate_ID"
and CLIENT_CA."Requisition_ID"=CLIENT_CD."Requisition_ID" )  
LEFT JOIN CLIENT_REQS ON CLIENT_REQS."Requisition_ID"=CLIENT_CA."Requisition_ID"  
LEFT JOIN CLIENT_RR
ON (CLIENT_CA."Candidate_ID"=CLIENT_RR."Candidate_ID" and CLIENT_CA."Requisition_ID"=CLIENT_RR."Requisition_ID" )  
GROUP BY CLIENT_CA."Candidate"
   ,CLIENT_CA."Requisition_ID" 
  ,CLIENT_CD."Gender"
  ,CLIENT_REQS."Requisition Title"
  ,CLIENT_REQS."Country" 
  ,CLIENT_RR."Status_Reason(E-Rec)" 
  ,CLIENT_CD."Application_Status";

technically this query works just fine and gives me exactly what I need with no errors. The thing is that I will need to add some 80 columns to the SELECT clause and the system or rather the logic is forcing me to put every column I add in there to also put it in the GROUP BY clause... I guess that's because of the "max" function. Basically CLIENT_CA is my "base" table and I am trying to achieve what in MS Excel would be done by the VLOOKUP function on the other 3 tables. I'm just a bit concerned that I am making this a bit unnecessarily complicated. Please do not hesitate to tell me I'm just plain stupid :) but only IF you have a suggestion how to do this better :) Thank you. oh.... I'm running this on dashDB.

Answer 1

use a subquery to extract from main table, join columns and calculated columns (entry, selection, offer) so you can limit the group by columns to a fixed number

SELECT  
CA."Requisition_ID",  
REQS."Requisition Title",  
REQS."Country",  
CD."Application_Status",  
CA."Candidate",  
CD."Gender",  
"ENTRY",  
"Selection",  
"Offer",  
RR."Status_Reason(E-Rec)"   
FROM ( 
    SELECT 
        "Requisition_ID", "Candidate_ID", "Candidate",
        max(case when "Process_of_Activity" = 'Application Entry' then "Completed_on" end) as "ENTRY",  
        max(case when "Process_of_Activity" = 'Candidate Selection' then "Completed_on" end) as "Selection",  
        max(case when "Process_of_Activity" = 'Job Offer' then "Completed_on" end) as "Offer"
    FROM CLIENT_CA
    GROUP BY "Candidate", "Requisition_ID", "Candidate_ID" 
    ) AS CA 
LEFT JOIN CLIENT_CD AS CD ON (CA."Candidate_ID"=CD."Candidate_ID" and CA."Requisition_ID"=CD."Requisition_ID" )  
LEFT JOIN CLIENT_REQS AS REQS ON REQS."Requisition_ID"=CA."Requisition_ID" 
LEFT JOIN CLIENT_RR AS RR ON (CA."Candidate_ID"=RR."Candidate_ID" and CA."Requisition_ID"=RR."Requisition_ID" )