Solved

SQL Command Limit in Crystal Reports

Posted on 2014-03-04
12
1,905 Views
Last Modified: 2014-03-04
Hey Guys,

I wanted to know if Crystal places a limit of the size an sql statement can be in a command. If so, can that size be changed?
0
Comment
Question by:metalteck
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 7

Expert Comment

by:Lee Ingalls
ID: 39903276
Crystal Reports XI R2 SP2 the maximum size of a SQL query in the SQL Command edit window is 64k.
0
 

Author Comment

by:metalteck
ID: 39903386
Is there a way I can increase that? I have a pretty big SQL query and need to see how I can get it into one command.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39903580
64k is over 8000 80-character lines.  You need more than that?

I assume you are using the alias option for tables so the full table name is used only in the FROM clause?

mlmcc
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 39903602
Can you put it in a stored procedure?
0
 

Author Comment

by:metalteck
ID: 39903799
I unfortunately can not use a stored procedure.
I've attached the code I'm using.
If you have any suggestions on how I can get this to work, I'll gladly try them.

Thanks
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 39903833
That's too bad, stored procedures make life much easier.

Doesn't look like the attachment took.  Maybe it's too big for EE also? :)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 100

Expert Comment

by:mlmcc
ID: 39903886
When you attach the file, make sure you add a comment

mlmcc
0
 

Author Comment

by:metalteck
ID: 39904119
0
 
LVL 7

Expert Comment

by:Lee Ingalls
ID: 39904172
1214 lines... I saved it at 66KB.
0
 

Author Comment

by:metalteck
ID: 39904216
Sounds about right. I eliminated as much as possible, but I know that new Account Units can be added at any time. Any Suggestions on how I can get this code to work?
0
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 500 total points
ID: 39904262
Lots of your case statements have the same "THEN" clause.  There is a great opportunity to reduce characters there.
For example, instead of having a line for each specific glm.ACCT_UNIT, only call out the exceptions as shown below.  Your where clause is already restricting the output to the ACCT_UNIT values you are concerned with.

CASE
        WHEN glm.ACCT_UNIT = '19000' THEN  'Sanford Hlth-Benmidji 19000'
        WHEN glm.ACCT_UNIT = '13700' THEN 'Anes Assoc of Jupiter 13700'
        WHEN glm.ACCT_UNIT = '10800' THEN 'Three Rivers Endoscopy 10800'
        WHEN glm.ACCT_UNIT = '18500' THEN 'Patriot (SINE) 18500'
      ELSE glm.[DESCRIPTION]
END
0
 

Author Closing Comment

by:metalteck
ID: 39904356
I was so concerned about getting the code right that I over looked that.
Thanks for the help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now