11/22/2024 3:55:08 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
Enable View Not Working - Access Violation
Posted: 15 Apr 10 3:12 PM
|
I've created a few views that provide the data structure necessary to build SLX groups for consolidated history and activity data. The version number is 7.5.2.2151. When I attempt to enable the view, I get an Access Violation error. I've profiled the action and that doesn't help. My view is below. Does anyone know why this is not working? Thanks!
SELECT
CASE IsNull(Histories.AccountID, '') WHEN '' THEN Activities.AccountID ELSE Histories.AccountID END AS AccountID,
CASE IsNull(Histories.UserName, '') WHEN '' THEN Activities.UserName ELSE Histories.UserName END AS UserName,
Histories.LastHistoryByUser, Activities.NextActivityByUser,
CASE IsNull(Activities.NextActivityByUser, '') WHEN '' THEN Histories.LastHistoryByUser ELSE Activities.NextActivityByUser END AS NextActivityLastHistoryCombined
FROM
(
--Last History (User)
SELECT h.ACCOUNTID, h.UserName, Max(CompletedDate) AS LastHistoryByUser
FROM sysdba.HISTORY h
INNER JOIN sysdba.C_ACTIVITYTYPE at ON at.CODE = h.TYPE
WHERE at.DESCRIPTION IN ('Phone Call', 'Meeting')
AND IsNull(h.ACCOUNTID, '') <> ''
GROUP by h.ACCOUNTID,h.USERNAME
) Histories
FULL OUTER JOIN (
-- Next Activity (User)
SELECT a.ACCOUNTID,
CASE u.UserID WHEN 'Admin' THEN 'Administrator' ELSE u.LASTNAME + ', ' + u.FIRSTNAME END AS USERNAME,
Min(a.STARTDATE) AS NextActivityByUser
FROM sysdba.ACTIVITY a
INNER JOIN sysdba.C_ACTIVITYTYPE at ON at.CODE = a.TYPE
INNER JOIN sysdba.USERINFO u ON u.USERID = a.USERID
WHERE at.DESCRIPTION IN ('Phone Call', 'Meeting')
AND IsNull(a.ACCOUNTID, '') <> ''
AND ACTIVITYID NOT IN (SELECT IsNull(ACTIVITYID, '') FROM sysdba.HISTORY)
GROUP by a.ACCOUNTID, CASE u.UserID WHEN 'Admin' THEN 'Administrator' ELSE u.LASTNAME + ', ' + u.FIRSTNAME END
) Activities ON Activities.ACCOUNTID = Histories.ACCOUNTID AND ISNULL(Activities.username, '') = ISNULL(Histories.Username, '') |
|
|
|
Re: Enable View Not Working - Access Violation
Posted: 18 Apr 10 11:46 AM
|
I have the exact same issue on a much simpler view that I built. I stripped out a lot of crap trying to get to the root of the problem but no luck. Same build number.
I was able to get one view enabled but I have three others I need that won't work.
CREATE VIEW [sysdba].[CrewMembers]
AS
SELECT
LastName + ', ' + FirstName as NameLF,
FirstName + ' ' + LastName as Name
FROM sysdba.CONTACT
WHERE (TYPE = 'Crew Member') OR
(TYPE = 'Installer')
I'm working on this right now and will let you know if I figure anything out.
Brian |
|
|
|
Re: Enable View Not Working - Access Violation
Posted: 18 Apr 10 12:16 PM
|
Ok. Seems like a bug in 7.5.2 with this build.
I gave up on it because I couldn't find an explanation and decided to find a workaround. The one view of the four that I was able to enable in 7.5.2 I created using the SQL function to execute the 'Create View' statement. I think that has a possibility of working but I tried a few more times and it did not work.
I found a workaround - and loaded m 7.5.2 databse in the 7.2 administrator, and enabled the 3 views without a problem. |
|
|
|
Re: Enable View Not Working - Access Violation
Posted: 18 Apr 10 9:19 PM
|
| I've received confirmation from Sage Support that this is a defect (obviously). There is currently a fix in customer validation that addresses this problem plus one with primary keys on views. If you contact tech support, they should be able to send you an early release of the new build. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|