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?
metalteckAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
UnifiedISConnect With a Mentor Commented:
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
 
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
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
 
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
 
metalteckAuthor Commented:
I was so concerned about getting the code right that I over looked that.
Thanks for the help.
0
All Courses

From novice to tech pro — start learning today.