Custom SQL and Crystal widgets

[WAndrewW]

Hi,
I am re-visiting Crystal Reports after prolonged absence. Here are my problems.
1. I need to connect to two databases. The first database returns a result set used in Modifying the Where part of an SQL statement (second database) for each row returned from the first database. The databases are on different machines. To put it another way I am creating my own join between two different databases. ( I know this can be done with various database tools and I can do so with Actuate )but can I do so in Crystal.
2. The SQL I need to send to the server creates a virtual table not a temp table but a list of columns returned that are created in the From Section of the SQL - the query is was written by the data architect and I must implement the query. Evidently he cannot implement a stored procedure nor a view in the database in question (Politics). Is there a way in Crystal to modify the SQL and assign the returned column values to one of the widgets that you place on the report design. Again I can do this in Actuate but have not worked with Crystal design for a number of revisions and years. Thanks for any help.

 

[kenhamady]

Question 1 sounds like a SQL subquery, with the WHERE clause of the main query using a list of values returned by a another SQL statement. If you have the Crystal SQL designer you can write your own SQL "view" (subquery and all) and report off of that using a QRY file. Or, if you are writing an application (like in VB) you can write your own SQL statement and pass the results to the report as a recordset.

Question 2 isn't as clear. Are you trying to take a column of values and make it appear as if it were 2 separate columns based on a condition? That would simply require 2 if-then formulas. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[WAndrewW]

I guess I did not explain clearly. The main point of question one is connecting to two databases on different physical machines and doing a distributed join.

Question 2 may be clarified by the sql. This is part of the SQL

SELECT
t.CUSIPNr AS Cusip,
t.PoolNr AS ContraAccount,
t.TranDt AS TradeDate,
countprice AS CountOfCusip,
MinPrice,
MaxPrice
FROM
ICITrade t JOIN
CDR_FI_Securities
ON (t.cusipnr=CDR_FI_Securities.cusipnr
and t.DATASOURCECD=CDR_FI_Securities.DATASOURCECD)
JOIN / * this is where minPrice, MaxPrice and
countprice come form */
select (
max(maxtrad.CurCalParPrAm) as maxprice,
min(maxtrad.CurCalParPrAm) as minprice,
count(*) as countprice
)
from
ICITrade maxtrad JOIN
CDR_FI_Securities maxsec ON
(maxtrad.cusipnr=maxsec.cusipnr )
/* I will spare you the rest of the SQL but as you can see maxprice, minprice, and countprice are not database tables. How do I display them on a report. */
Andrew

 

[kenhamady]

The problem may be that I am bumping the limits of my SQL knowledge. I am not familiar with distributed joins.

But for question 2, since these values are named in your SELECT, I would think that they would be columns in the result set. So, if you can run a report off of the results of this statement, you should see these items as available fields to the report.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com