|
Okay, here's
my SQL statement. So I decided that all I wanted was a count of SELECT CASE WHEN LEFT(lcase(b.emp_last_name),1) BETWEEN 'a' AND 'g' THEN 1 WHEN LEFT(lcase(b.emp_last_name),1) BETWEEN 'h' AND 'p' THEN 2 WHEN LEFT(lcase(b.emp_last_name),1) BETWEEN 'q' AND 'z' THEN 3 ELSE 4 END AS GroupID,a.Line_ID,a.Client_ID,a.order_number,a.cost_center_id,a.classification_id,a.emp_id,a.shift_id,a.scheduled_date, aa.shift_name,aa.start_time,aa.end_time,b.client_emp_id,b.emp_first_name AS emp_first_name,b.emp_middle_name,b.emp_last_name AS emp_last_name, lcase(b.emp_first_name) || ' ' || lcase(b.emp_last_name) AS SortField,b.emp_classification_id,bb.classification_name,c.cost_center_name, '' AS Order_Status,d.NoteID,e.NoteID AS ChargeFlag,f.NoteID AS OnCallFlag,g.NoteID AS LateFlag FROM MCDev.Schedule_Line AS a LEFT OUTER JOIN CommonDev.Client_Shifts AS aa ON a.shift_id = aa.shift_id LEFT OUTER JOIN MCDev.Employee AS b ON a.emp_id = b.emp_id LEFT OUTER JOIN CommonDev.Client_Classifications AS bb ON a.classification_id = bb.classification_id LEFT OUTER JOIN CommonDev.Cost_Center AS c ON a.cost_center_id = c.cost_center_id LEFT OUTER JOIN MCDev.Notes AS d ON a.emp_id = d.EmployeeID AND a.scheduled_date = d.ShiftDate AND d.NoteType = 'User' LEFT OUTER JOIN MCDev.Notes AS e ON a.emp_id = e.EmployeeID AND a.scheduled_date = e.ShiftDate AND e.NoteType = 'Flag' AND e.NoteText = 'Charge' AND e.FlagStatus = 'Set' LEFT OUTER JOIN MCDev.Notes AS f ON a.emp_id = f.EmployeeID AND a.scheduled_date = f.ShiftDate AND f.NoteType = 'Flag' AND f.NoteText = 'On Call' AND f.FlagStatus = 'Set' LEFT OUTER JOIN MCDev.Notes AS g ON a.emp_id = g.EmployeeID AND a.scheduled_date = g.ShiftDate AND g.NoteType = 'Flag' AND g.NoteText = 'Late' AND g.FlagStatus = 'Set' WHERE 1=1 AND (b.emp_active = 'Yes' OR a.Order_Number <> '') AND a.client_id = 90 AND a.cost_center_id = 792 AND a.scheduled_date BETWEEN {d '2006-07-02'} AND {d '2006-07-22'} ORDER BY c.cost_center_name,aa.shift_name,bb.classification_name,a.scheduled_date,b.emp_last_name,b.emp_first_name And here's the explaination: This query's intent is to return a record set containing one record for each employee scheduled. So if Joe Lunchbox is scheduled on the 17th, 18th and 19th, then he will have three records in the result. No problems there. However, there is the possibility of attaching notes and or flags to any of these records. An employee might be flagged as late and have a corresponding note, while they might also just have an arbitrary note attached to them on that day. Notes and flags are stored in the same table. A flag is simply a certain type of note. The problem occurs because while any given record can only have one late flag, one on call flag and one charge flag, they could have multiple notes, and having multiple notes causes multiple records in the result set. :( So I decided that what I needed was only a count of the notes since the purpose of the last four outer joins is only really to tell me if the note exists and not to get the actual content of the note. So I made the following changes to the statement above. Instead of: ... d.NoteID,... it reads: ... COUNT(d.NoteID) AS NoteCount, ... And that nets me the following error: [SQL0122] Column EMP_LAST_NAME or _expression_ in SELECT list not valid. What the heck? I'm sure I'm missing something basic here... or maybe something not so basic. Help would be appreciated. :) Chris |
_______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
