Print Page | Close Window

SQL Expression field with vaiables

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11756
Printed Date: 04 May 2024 at 4:13pm


Topic: SQL Expression field with vaiables
Posted By: DanneO
Subject: SQL Expression field with vaiables
Date Posted: 01 Dec 2010 at 2:05am
Hi!

How can I use variables in an SQL field expression?
For example:

(
SELECT COUNT(*) FROM customer WHERE sysid =  '{?sysid}'
)

This does not work. Is it just because my old version 8.5?

Thanks in advance!
/Dan


Replies:
Posted By: hilfy
Date Posted: 01 Dec 2010 at 11:52am
I haven't tried to use parameters/variables in a SQL expression but, you should NOT be doing a full select statement in a SQL Expression - that's not really what they're designed for and I haven't seen that work correctly in any version.  That may be what the real problem is here.
 
-Dell
 
 


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Keikoku
Date Posted: 02 Dec 2010 at 5:13am
I wasn't aware that you could use crystal variables/parameters in a SQL expression in the first place.

Then again, I'm not really sure how crystal goes about evaluating things.

Posted By: DanneO
Date Posted: 02 Dec 2010 at 5:41am
That is actually the question. I'm not sure if it is possible

/Dan

Posted By: Keikoku
Date Posted: 02 Dec 2010 at 5:59am
OH. Haha I see.

Where are you typing it though? I'm not using 8.5 but for me the only place I can type SQL statements is when I'm retrieving from the database. Maybe I can figure something out if I knew where to look.

Posted By: hilfy
Date Posted: 02 Dec 2010 at 7:19am
Keikoku,
 
If you're NOT using commands, you can define SQL expressions in the Field Explorer.  I mostly use commands, but when I'm not, I've used SQL expressions to call functions that do things like return addresses or names based on multiple fields.
 
Basically, when you create a SQL expression, it is added to the "Select" portion of the query that Crystal generates based on the tables and links defined in the report.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Keikoku
Date Posted: 02 Dec 2010 at 7:28am
oh, that's interesting. I haven't made any reports without using commands yet.

Anyways it seems like SQL expression fields don't support parameters :(

http://www.tek-tips.com/faqs.cfm?fid=1377


2) The only fields available for use in a SQL Expression are true database table fields. You cannot incorporate Crystal functions, Parameters or Special Fields into a SQL Expression like you can in a Formula.


Print Page | Close Window