Make crystal see changes to sql query

[SarahMS]

Crystal 8, sql 2000

I am trying to change a table from inside the sql query window, but I can't make crystal see the fields. It doesn't give me an error, it just won't see it. If I were to change the table in sql, I would verify database to make it see it - how can I make it see the changed sql query?

I think that what I'm doing would be called a left outer join to a derived table. I'm changing ...

SELECT
Warrants."ID", Warrants."Fund", Warrants."WarrantNo", Warrants."payyear", Warrants."date101", Warrants."PR", Warrants."PRType", Warrants."vendor", Warrants."printwar", Warrants."amount", Warrants."Link3", Warrants."district",
Vendors."ssno",
warrantcomment."E1",
Object."Typ", Object."Key1"
FROM
{ oj ((("RMKRS"."dbo"."Warrants" Warrants INNER JOIN "RMKRS"."dbo"."v_Funds" v_Funds ON
Warrants."Link1" = v_Funds."id&quot
INNER JOIN "RMKRS"."dbo"."Object" warrantcomment ON
Warrants."ID" = warrantcomment."ID&quot
INNER JOIN "RMKRS"."dbo"."Vendors" Vendors ON
Warrants."Link3" = Vendors."id&quot
INNER JOIN "RMKRS"."dbo"."Object" Object ON
v_Funds."link1" = Object."ID"}
WHERE
Object."Typ" = 56
ORDER BY
Warrants."vendor" ASC,
Warrants."district" ASC,
Warrants."ID" ASC

To...


SELECT
Warrants."ID", Warrants."Fund", Warrants."WarrantNo", Warrants."payyear", Warrants."date101", Warrants."PR", Warrants."PRType", Warrants."vendor", Warrants."printwar", Warrants."amount", Warrants."Link3", Warrants."district",
Vendors."ssno",
warrantcomment."E1",
Object."Typ", Object."Key1",
district."distname"
FROM
{ oj (((("RMKRS"."dbo"."Warrants" Warrants INNER JOIN "RMKRS"."dbo"."v_Funds" v_Funds ON
Warrants."Link1" = v_Funds."id&quot
INNER JOIN "RMKRS"."dbo"."Object" warrantcomment ON
Warrants."ID" = warrantcomment."ID&quot
INNER JOIN "RMKRS"."dbo"."Vendors" Vendors ON
Warrants."Link3" = Vendors."id&quot
INNER JOIN "RMKRS"."dbo"."Object" Object ON
v_Funds."link1" = Object."ID&quot
left outer join
(select case when count(distinct po.c2)>1 then 'Multiple' else max(po.c2) end as distname, detail.link3 as warid
from object po, object detail where
detail.link1=po.id and
po.typ=62 and detail.typ=67 group by detail.link3) district on
warrants.id=district.warid}
WHERE
Object."Typ" = 56
ORDER BY
Warrants."vendor" ASC,
Warrants."district" ASC,
Warrants."ID" ASC

 

[kenhamady]

Are you in Crystal Reports in the SHOW SQL window, or are you in the SQL Designer? 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

 

[SarahMS]

It is in the show SQL window. Guess that was the problem - it probably wasn't designed to be modified from there. Yesterday I was able to work around the problem outside of Crystal, but I'm interesed in using the crystal queries in the future.

I've never used the SQL Designer, but if I understand it correctly, if I used it, I would have to deploy the report and the .qry to the customer, right? We use ODBC drivers, so I would have to have the same ODBC name also, wouldn't I?

Thanks,
Sarah

 

[Rooble]

Yes, you will have to place the .qry file and the DSN onto the client pc from where the Report is being generated. Mind it, you will hit some performance issues using the .qry file and if you have any aggregate functions/calculations then even more.

 

[Rooble]

Yes, you will have to place the .qry file and the DSN onto the client pc from where the Report is being generated. Mind it, you will hit some performance issues using the .qry file and if you have any aggregate functions/calculations then even more.

Rooble

 

[SarahMS]

Thanks for the tip on aggregate functions.

I know that Crystal isn't as efficient in it's joining as SQL is at the backend. Do you know if the qry files are faster than Crystal? Is it as fast as SQL?

Thanks,
Sarah

 

[kenhamady]

You can't change the Select in the Show SQL window.

You can do more SQL in the QRY, but this has to go through ODBC so there are some limitations. The most efficient is to write a stored procedure in the DB and run the report off of the SP. 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