Improve company productivity with a Business Account.Sign Up

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

SQL Command Limit in Crystal Reports

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
metalteck
Asked:
metalteck
  • 5
  • 3
  • 2
  • +1
1 Solution
 
Lee IngallsDirector of IT/TS, Quality and FinanceCommented:
Crystal Reports XI R2 SP2 the maximum size of a SQL query in the SQL Command edit window is 64k.
0
 
metalteckAuthor Commented:
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
 
mlmccCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
UnifiedISCommented:
Can you put it in a stored procedure?
0
 
metalteckAuthor Commented:
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
 
UnifiedISCommented:
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
 
mlmccCommented:
When you attach the file, make sure you add a comment

mlmcc
0
 
metalteckAuthor Commented:
0
 
Lee IngallsDirector of IT/TS, Quality and FinanceCommented:
1214 lines... I saved it at 66KB.
0
 
metalteckAuthor Commented:
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
 
UnifiedISCommented:
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
 
metalteckAuthor Commented:
I was so concerned about getting the code right that I over looked that.
Thanks for the help.
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now