Replace entire SQL Stmt at runtime

[week]

Hi,

I have an existing report which I am trying to an additional table to it. After I add the table through Database Visual Linking Expert, the Show SQL Query doesn't pick up the new table. I edit the Query manually and reset the location. Tried to run the report, I get an error "ORA-01007 variable not in select list." indicating problem in recognizing sub query that is part of "Select".

My questions is
1.Why it's giving DLL error when the same query had no problem before adding a new table.

2. I am thinking of replacing the whole query during run time but don't know how to do it.

Does anyone have the answer to any of the questions above?

Thanks a bunch!

 

[rogar]

CR builds its SQL Query using the tables and links from the visual linking expert and the fields you have either placed on the report design or referenced in formulas or groupings. You are not allowed to edit the SELECT part of the query in the View SQL dialog and you cannot use subqueries at all. These are limitations of CR and we have to live with them. If you need to use more complex queries, your options are: database stored procedures, Crystal Queries from the Crystal SQL Designer, and programmed front ends in VB or asp.

 

[week]

Where do I find CR SQL Desinger? You mean Selection Formular?

Part of the Where is built in asp (VB) but I don't think I can replace the whole query with that. How does the query from asp work with the built in SQL stmt in the report? I don't supposed I can get rid of the CR Query since it reflects the tables the report is using.

Thanks.

 

[rogar]

SQL Designer is a separate application that is part of the CR installation package. It creates a .qry file that can be used as a data source for a report. It sound like you are already using some sort of front end to call CR(??). You can pass a record selection formula or parameter values or a recordset from your front end to the CR object - the syntax will depend on the integration method you are using. However you do it, what you pass in must match the fields in the report design or you will get errors.

 

[week]

But is the new query (that I am passing to the report) ignore the old query built by Crystal?

 

[rogar]

Don't know - I've never done that. What integration method are you using -- RDC, OCX, CRPE? -- and from what environment -- VB, web page, other??