| Author |
Message |
nmercure
Newbie
Joined: 25 Sep 2007
Location: United States
Online Status: Offline
Posts: 12
|
   Posted: 11 Oct 2007 at 11:51am |
|
Whoops....spoke too soon. It runs from query analyzer, but locks up Crystal and my database when I run it from Crystal.
|
 IP Logged |
|
jkwrpc
Senior Member
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
|
Posted: 11 Oct 2007 at 2:51pm |
I find that Query Analyzer puts in too much stuff that is not needed and makes it hard to read. Makes troubleshooting harder for me. Give this SQL a try. Run it in Query Analyzer first then if it is working run it in CR:
SELECT
CallLog.CallID,
CallLog .ClosedDate,
CallLog .RecvdDate,
CallLog .CallType,
CallLog .CallStatus,
CallLog .Priority,
CallLog .RecvdTime,
CallLog .ClosedTime,
Asgnmnt.Assignee,
Asgnmnt.GroupName,
Asgnmnt.DateAssign,
Asgnmnt.DateResolv,
CallLoog.CallDesc
FROM CallLog
LEFT OUTER JOIN dbo.Asgnmnt ON CallLog.CallID = Asgnmnt.CallID
GROUP BY CallLog.CallID, Asgnmnt.Assignee,
HAVING CallLog.CallStatus = 'Closed' AND COUNT (Asgnmnt.Assignee) = 1
Regards,
John W.
|
|
IP Logged |
|
nmercure
Newbie
Joined: 25 Sep 2007
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 12 Oct 2007 at 5:39am |
Now this looks more like something I can understand.......I don't mind saying I am really in over my head here. My co-worker is the one that ran it in Query Analyzer which I know absolutely nothing about.
Anyway, I used your SQL and it doesn't like 'having'. Do you know what would be an alternative to that word ?
Thanks John,
Nancy
|
|
IP Logged |
|
jkwrpc
Senior Member
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
|
Posted: 12 Oct 2007 at 6:53am |
I will guess there is an error because call status is not in the the Group By statement. Having is an SQL keyword and should run in any SQL compliant database.
Query Analyzer is an MS SQL tool that allows you to create and run SQL statements. If you use its query builder feature to create statements it tends to create them the way it thinks it should be. I like mine cleaner.
Try this one I moved call status to a WHERE statement
SELECT
CallLog.CallID,
CallLog .ClosedDate,
CallLog .RecvdDate,
CallLog .CallType,
CallLog .CallStatus,
CallLog .Priority,
CallLog .RecvdTime,
CallLog .ClosedTime,
Asgnmnt.Assignee,
Asgnmnt.GroupName,
Asgnmnt.DateAssign,
Asgnmnt.DateResolv,
CallLoog.CallDesc
FROM CallLog
LEFT OUTER JOIN dbo.Asgnmnt ON CallLog.CallID = Asgnmnt.CallID
WHERE CallLog.CallStatus = 'Closed'
GROUP BY CallLog.CallID, Asgnmnt.Assignee,
HAVING COUNT (Asgnmnt.Assignee) = 1
Regards,
John W.
|
|
IP Logged |
|
nmercure
Newbie
Joined: 25 Sep 2007
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 12 Oct 2007 at 7:00am |
|
It choked on Having again. Incorrect syntax near the keyword 'having'.
|
|
IP Logged |
|
jkwrpc
Senior Member
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
|
Posted: 12 Oct 2007 at 8:27am |
I hate it when this happens, dont you. Its a problem not being able to test the code.
So here try this one. There was a comma at the end of the Group By statement (after Asgnmnt.Assignee)that should not have been there. That will raise the error you have been seeing. Its always the little stuff. Hope this fixes it for you.
----------------------------------
SELECT
CallLog.CallID,
CallLog .ClosedDate,
CallLog .RecvdDate,
CallLog .CallType,
CallLog .CallStatus,
CallLog .Priority,
CallLog .RecvdTime,
CallLog .ClosedTime,
Asgnmnt.Assignee,
Asgnmnt.GroupName,
Asgnmnt.DateAssign,
Asgnmnt.DateResolv,
CallLoog.CallDesc
FROM CallLog
LEFT OUTER JOIN dbo.Asgnmnt ON CallLog.CallID = Asgnmnt.CallID
WHERE CallLog.CallStatus = 'Closed'
GROUP BY CallLog.CallID, Asgnmnt.Assignee
HAVING COUNT (Asgnmnt.Assignee) = 1
Regrards
John W.
|
|
IP Logged |
|
nmercure
Newbie
Joined: 25 Sep 2007
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 12 Oct 2007 at 12:56pm |
Now I get column 'Calllog.ClosedDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause'
Thanks so much again for your help.
|
|
IP Logged |
|
jkwrpc
Senior Member
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
|
Posted: 12 Oct 2007 at 1:15pm |
Try this one
=============================
SELECT
CallLog.CallID,
CallLog .ClosedDate,
CallLog .RecvdDate,
CallLog .CallType,
CallLog .CallStatus,
CallLog .Priority,
CallLog .RecvdTime,
CallLog .ClosedTime,
Asgnmnt.Assignee,
Asgnmnt.GroupName,
Asgnmnt.DateAssign,
Asgnmnt.DateResolv,
CallLoog.CallDesc
FROM CallLog
LEFT OUTER JOIN dbo.Asgnmnt ON CallLog.CallID = Asgnmnt.CallID
WHERE CallLog.CallStatus = 'Closed'
GROUP BY
CallLog.CallID,
CallLog .ClosedDate,
CallLog .RecvdDate,
CallLog .CallType,
CallLog .CallStatus,
CallLog .Priority,
CallLog .RecvdTime,
CallLog .ClosedTime,
Asgnmnt.Assignee,
Asgnmnt.GroupName,
Asgnmnt.DateAssign,
Asgnmnt.DateResolv,
CallLoog.CallDesc
HAVING COUNT (Asgnmnt.Assignee) = 1
=======================
There can't be much more to try...
Regards,
John W.
|
|
IP Logged |
|
nmercure
Newbie
Joined: 25 Sep 2007
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 12 Oct 2007 at 2:13pm |
I am a true pain in the b@tt.
Now I get The text, ntext,, and image data types cannt be compared or sorted, except when using IS NULL or LIKE operator.
John, I truly don't mind if you give up on me. This is more than you bargained for.....
|
|
IP Logged |
|
jkwrpc
Senior Member
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
|
Posted: 12 Oct 2007 at 8:58pm |
Actually it me that needs to offer an apology. The last SQL statement I posted wont give you what you want. While it may have made me feel better it was not much help in the end. It was me being frustrated by not being able to run the code on my end and give you a correct answer.
I believe we need to go back to using table aliases. I will work on creating a more thoughtful answer this weekend.
My apologies, and you are not the pain. Hang in there we will get you a working solution or at least to the point where you come up with the solution.
Regards,
John W.
|
|
IP Logged |
|
|
|
