Crystal Reports SQL Question

Technical Questions

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

Topic: SQL Question

Author	Message
VORT8019 Newbie Joined: 25 Sep 2013 Online Status: Offline Posts: 3	Topic: SQL Question Posted: 06 Feb 2014 at 10:12am
	I am new to SQL. I have made one of my reports using only the SQL command in database expert, however I had to use the standard crystal methods for the below two formulas Formula: if {Table.XYZ} = "Apples" then 1 else 0 then in select expert I have this as a group selection formula maximum({@Formula}, {Table2.ABC}) = 1 Is there a way to write this into the SQL command?
	IP Logged


kostya1122 Senior Member Joined: 13 Jun 2011 Online Status: Offline Posts: 475	Posted: 06 Feb 2014 at 11:09am
	select {Table2.ABC}, max(case when {Table.XYZ} = 'Apples' then 1 else 0 end) as [field name] from sometable group by {Table2.ABC} having max(case when {Table.XYZ} = 'Apples' then 1 else 0 end) = 1
	IP Logged

VORT8019 Newbie Joined: 25 Sep 2013 Online Status: Offline Posts: 3	Posted: 07 Feb 2014 at 2:16am
	How do I embed this into the rest of the SQL code that is very lengthy already?
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 07 Feb 2014 at 5:24am
	couldn't he just add to the existing SQL isApple = CASE WHEN {table.xyz}='apples' then 1 else 0 end, then in the record selector: {command.isApple} = 1 or better yet, since he doesn't seem to want apples at all, couldn't he just add to the WHERE clause of the command: where {table.xyz} = 'Apples' just wondering...
	IP Logged

VORT8019 Newbie Joined: 25 Sep 2013 Online Status: Offline Posts: 3	Posted: 07 Feb 2014 at 5:59am
	Works!
	IP Logged

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