Hi There,
Very new to SQL so please bear with me. I am trying to run a report that dumps all the information on a users profile for users of a certain role. I seem to be running into a problem where the SQL statement is so long, it's being truncated.
Maybe there is an easier way of doing this that results in a smaller SQL statement? The code below is only how far I got before I ran into this problem.. there are more fields I would like to have added...
SELECT U.Username, U.FirstName, U.LastName, U.Email,
(SELECT PropertyValue
FROM pr_UserProfile
WHERE (UserID = U.UserID) AND (PropertyDefinitionID = 31)) AS PhoneNum,
(SELECT PropertyValue
FROM pr_UserProfile AS pr_UserProfile_2
WHERE (UserID = U.UserID) AND (PropertyDefinitionID = 25)) AS Unit,
(SELECT PropertyValue
FROM pr_UserProfile AS pr_UserProfile_2
WHERE (UserID = U.UserID) AND (PropertyDefinitionID = 26)) AS Street,
(SELECT PropertyValue
FROM pr_UserProfile AS pr_UserProfile_2
WHERE (UserID = U.UserID) AND (PropertyDefinitionID = 27)) AS strCity,
(SELECT PropertyValue
FROM pr_UserProfile AS pr_UserProfile_2
WHERE (UserID = U.UserID) AND (PropertyDefinitionID = 28)) AS strRegion,
(SELECT PropertyValue
FROM pr_UserProfile AS pr_UserProfile_2
WHERE (UserID = U.UserID) AND (PropertyDefinitionID = 30)) AS strPostalCode,
(SELECT PropertyValue
FROM pr_UserProfile AS pr_UserProfile_2
WHERE (UserID = U.UserID) AND (PropertyDefinitionID = 31)) AS strTele,
(SELECT PropertyValue
FROM pr_UserProfile AS pr_UserProfile_2
WHERE (UserID = U.UserID) AND (PropertyDefinitionID = 45)) AS strMobile
FROM pr_UserPortals AS UP RIGHT OUTER JOIN
pr_UserRoles AS UR INNER JOIN
pr_Roles AS R ON UR.RoleID = R.RoleID RIGHT OUTER JOIN
pr_Users AS U ON UR.UserID = U.UserID ON UP.UserId = U.UserID
WHERE (UP.PortalId = 0) AND (R.RoleName = 'Birdline Member')
ORDER BY U.LastName
When I paste this in the code box then apply it, it is truncted and the last few lines lost.
So, any ideas how I can get round this ?
cheers