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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
metalteckAuthor Commented:
I was so concerned about getting the code right that I over looked that.
Thanks for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.