That works! Thanks Dave! :) And you are correct I am on
an AS/400 running DB2.
I've not heard of COALESCE before. I'll have to look it up and figure
it out.
Sweet! Thanks again. :)
Chris
David L. Penton wrote:
You
would have had to put a large GROUP BY in that as well (you may have?)
but...
This is the best I can think of right now. IIRC you are on DB2 (on
AS/400?) but DB2 nonetheless. DB2 doesn't seem to mind rather complex
WHERE clauses and CASE statements. In saying that, I think you can get
rid of the 'e, f and g' joins as well in favor of the derived view
below. Instead of returning the NoteID, I have it set to return Y/N
instead (easy enough to put it back to the former if needed):
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 || ' ' || b.emp_last_name) AS SortField
, b.emp_classification_id
, bb.classification_name
, c.cost_center_name
, '' AS Order_Status
, COALESCE(dn.HasUserNote, 'N') HasUserNote
, COALESCE(dn.ChargeFlag, 'N') ChargeFlag
, COALESCE(dn.OnCallFlag, 'N') OnCallFlag
, COALESCE(dn.LateFlag, 'N') 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
(
SELECT
n.EmployeeID, n.ShiftDate
, max(CASE WHEN n.NoteType = 'User'
THEN 'Y' ELSE 'N' END) HasUserNote
, max(CASE WHEN n.NoteText = 'Charge'
THEN 'Y' ELSE 'N' END) ChargeFlag
, max(CASE WHEN n.NoteText = 'On Call'
THEN 'Y' ELSE 'N' END) OnCallFlag
, max(CASE WHEN n.NoteText = 'Late'
THEN 'Y' ELSE 'N' END) LateFlag
FROM
MCDev.Notes n
WHERE
n.NoteType = 'User'
OR (
n.NoteType = 'Flag'
AND n.NoteText IN ('Charge', 'On Call', 'Late')
AND n.FlagStatus = 'Set'
)
GROUP BY
n.EmployeeID, n.ShiftDate
) AS dn ON
a.emp_id = dn.EmployeeID
AND a.scheduled_date = dn.ShiftDate
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
- dave
Christopher Jordan wrote:
Okay, here's my SQL statement.
So I decided that all I wanted was a count of
<snip />
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/
|
