Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Link loop Post Reply Post New Topic
<< Prev Page  of 3 Next >>
Author Message
elie234
Groupie
Groupie


Joined: 21 Aug 2009
Online Status: Offline
Posts: 68
Quote elie234 Replybullet Posted: 11 Sep 2009 at 12:23pm

Your idea sounds good but 2 q's (Please forgive my ignorance I am new to SQL commands):
1. Right now I don't have the X table in the command and am still linking it to Y and Z tables in db expert. I also have other tables linking from X (X is the main table in this report). So if I link from X to Y and Z in the command would it still be ok to link from X to other tables in db expert?
2. What would the SQL expression be to convert B into A?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Sep 2009 at 2:03pm
No worries. I muddle through this myself. There are others on this site that can be much more help with SQL statements...
1. You can include tableX straight into the command but I am not sure it will give you the fields set that you want it to. It is really hard to say without the data and DB architecture. It might alter your results if you do this. You can get  fancy with SQL statements where you have basically a query inside a query. If you are just learning SQL this might be too much to try but look for examples. You will proabably need it in the future or you will learn a great process to manipulte data before you get it ito crystal which can make the crystal design much more relaxing and straight forward.
If you need to add the conversion to the "X" table you can just replace the whole table with a second command and use that exactly the same as you are uing your X table.
 
Select *, (CASE when tablex.machinefield='B' then 'A' ELSE tablex.machinefield END) as your_desired_output_name_here
From tablex
 
2. The blue section in the statement above should convert the B to A. It is really only needed if there are more values then A or B. If it can only be A or B (and no NULLS or blanks) then you can just add a field instead of the case statement: 'A' as your desired ouput name
 
Just some notes. I have found for my set up that commands really bog down performance. I use SQL as my source and I create views or stored procedures and use them as the source if the tables need to be manipulated. This is basically the same thing as a Command but I have more control, performance is not impacted and I can reuse these views for mutliple reports. The GUI is nice for building them as well (views not SPs).


Edited by DBlank - 11 Sep 2009 at 2:06pm
IP IP Logged
elie234
Groupie
Groupie


Joined: 21 Aug 2009
Online Status: Offline
Posts: 68
Quote elie234 Replybullet Posted: 14 Sep 2009 at 10:45am
Thanks so much for all your help. I am giving the SQL command a shot. I am getting a syntax error though. If I just do Select (CASE.....) as A from table X then it works. But if I do Select *, and then the CASE statement I get a syntax error.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Sep 2009 at 10:52am
try and use each field you want to use from the table instead of the * (all) ....
select fieldname1, fieldname2, fieldname3,...,(Case ...)
IP IP Logged
elie234
Groupie
Groupie


Joined: 21 Aug 2009
Online Status: Offline
Posts: 68
Quote elie234 Replybullet Posted: 14 Sep 2009 at 10:56am
Thanks the individual fields work.
IP IP Logged
elie234
Groupie
Groupie


Joined: 21 Aug 2009
Online Status: Offline
Posts: 68
Quote elie234 Replybullet Posted: 14 Sep 2009 at 11:21am
How do I create a command that can reference two different databases? When I create the command I have to start it from one db and it is not recognizing the other.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Sep 2009 at 11:36am
The same way you would a view or stored proc in SQL using a join type. Also if you are selecting fields from more than one table/source then you have to start each selected field with that source e.g. instead of fieldname1 use tableA.fieldname1 ).
an example:
SELECT tableA.fieldname1, tableA.fieldname2, tableA.fieldname3, tableB.fieldname1, ....
FROM  
table A INNER JOIN table B ON tableA.field1=tableB.field1
IP IP Logged
elie234
Groupie
Groupie


Joined: 21 Aug 2009
Online Status: Offline
Posts: 68
Quote elie234 Replybullet Posted: 14 Sep 2009 at 11:57am
When I try to do the join I am getting a syntax error saying the other db is an unknown catalog.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Sep 2009 at 12:15pm
Not sure about that one.
You have access to creating views or stored procs in your SQL DB, correct?
I would move away fom the commands and create a view to use in the DB.
In the Enterprise Managee the GUI interface is relatively easy to use.
Expand your DB
Right click on the Views and select New View
This is a decent GUI interface to add tables, drag and drop your joins. Change them from inner to outer, plce conditions, add select criteria, etc.
it will write the SQL as you use the GUI.
You are suppose to be able to write case statements in the Column section although mine chokes on this option where as on my coworkers it is fine. Never worried about it as I can write those elsewhere.
Anyway this might be a better solution for you.
Is there any SQL DB folks nearby that can help you write this or teach you the interface?


Edited by DBlank - 14 Sep 2009 at 12:16pm
IP IP Logged
elie234
Groupie
Groupie


Joined: 21 Aug 2009
Online Status: Offline
Posts: 68
Quote elie234 Replybullet Posted: 14 Sep 2009 at 12:23pm
I thought about trying a view but I don't see an option for it. When I expand the DB in the expert I only see Add command and the tables. Does this mean I don't have access?
Unfortunantely we don't have any SQL DB people here.
IP IP Logged
<< Prev Page  of 3 Next >>
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.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.