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

you might be better off using the reports module (http://www.dotnetnuke.com/tabid/125/default.aspx) for this kind of thing - http://www.dotnetnuke.com/Community/Blogs/tabid/825/BlogID/196/Default.aspx has a bunch of blog entries that might help give you an idea of the capabilities.

Cathal