Crystal Reports SQL Expression field with vaiables

Technical Questions
Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions

Topic: SQL Expression field with vaiables

Author	Message
DanneO Newbie Joined: 01 Dec 2010 Online Status: Offline Posts: 2	Topic: SQL Expression field with vaiables 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
	IP Logged

hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3701	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 www.protiviti.com/US-en/data-management-advanced-analytics
	IP Logged

Keikoku Senior Member Joined: 01 Dec 2010 Online Status: Offline Posts: 386	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. Edited by Keikoku - 02 Dec 2010 at 5:14am
	IP Logged

DanneO Newbie Joined: 01 Dec 2010 Online Status: Offline Posts: 2	Posted: 02 Dec 2010 at 5:41am
	That is actually the question. I'm not sure if it is possible /Dan
	IP Logged

Keikoku Senior Member Joined: 01 Dec 2010 Online Status: Offline Posts: 386	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. Edited by Keikoku - 02 Dec 2010 at 6:26am
	IP Logged

hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3701	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 www.protiviti.com/US-en/data-management-advanced-analytics
	IP Logged

Keikoku Senior Member Joined: 01 Dec 2010 Online Status: Offline Posts: 386	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. Edited by Keikoku - 02 Dec 2010 at 7:30am
	IP Logged

Post Reply

Post New Topic

Printable version

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

This page was generated in 0.031 seconds.