Solved

SQL Command Limit in Crystal Reports

Posted on 2014-03-04
12
2,022 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 8

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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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 8

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

838 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