Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1476
  • Last Modified:

Recursive Query in Crystal Reports on SQL Server

Good morning -
I have a recursive query on two tables:
ADUsers and Groups with about 24,000 records
Groups with about 120,000 records  (With selection criteria - this is whittled down to 4600)

Here's the query:
WITH EmployeeList AS
(SELECT      A.DistinguishedName,
                      A.AccountName,
                      A.DisplayName,
                      A.Type,
                      B.MEMBEROF,
                      B.USERNAME
FROM         ADUsersAndGroups A,
                    GROUPS B
WHERE     A.type = 'User'
AND           B.MEMBEROF LIKE '%HEALTH_%'
                 
UNION ALL

SELECT      A.DistinguishedName,
                      A.AccountName,
                      A.DisplayName,
                      A.Type,
                      B.MEMBEROF,
                     B.USERNAME
FROM         ADUsersAndGroups A,
                    GROUPS B
WHERE     A.type = 'Group'
AND           B.MEMBEROF LIKE '%HEALTH_%')
SELECT * FROM EmployeeList  

Problem:  When I attempt to run this in Crystal Reports I'm put in an infinite loop... with last record count at about 32M records before my system ran out of memory.    When I change the syntax to just UNION or limit the record count, Crystal Reports locks up after 5-10 mins when I try to save the command window.

Is there a more efficient or better way to handle this?   I tried using the sub report approach but have issues with the output and subreports using CSV formatting.

Any help would be appreciated.    Thanks, Mojeaux
0
mojeaux
Asked:
mojeaux
3 Solutions
 
vastoCommented:
You need ; at the end of
SELECT * FROM EmployeeList  


SELECT * FROM EmployeeList  ;
0
 
mlmccCommented:
You need to join the tables.   What you have now is a cross join between ADUsersandGroups and GROUPS.

Every user record in ADUsersandGroups is selected then joined to every record in B where Memberof has Health in the name.  Similarly for the second query.

mlmcc
0
 
Kevin CrossChief Technology OfficerCommented:
In addition, for recursion you should have EmployeeList as a JOIN to the second query as well as a filter condition that stops recursion at some point.
0
 
mojeauxAuthor Commented:
Thank you for catching the syntax and the additional information regarding the second query.   I will give these a try.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now