Complex SQL queries
I need to modify the SQL statement before to FR executes it. Parameters it is not enough.
In FR4, I replaced some tags before the preview. For example my SQL statement is:
SELECT * FROM Customer /*WHERE %%MyFilterTag%% ORDER BY %%MyOrderTag%% */
Then, from the source, I replaced %%MyFilterTag%% by my custom filter and %%MyOrderTag%% by my custom sort.
The /* .....*/ is because if I still do not have the tags, the preview window can work without replacing the tags.
It has been working very well in FR4. Now, in FR.Net I have a problem: It deletes the SQL statement.
I mean, I enter the SQL statement, I save the report, I open it again and if I edit the SQL, it has disappeared.
I am using a Firebird connection.
Is there a solution?
In FR4, I replaced some tags before the preview. For example my SQL statement is:
SELECT * FROM Customer /*WHERE %%MyFilterTag%% ORDER BY %%MyOrderTag%% */
Then, from the source, I replaced %%MyFilterTag%% by my custom filter and %%MyOrderTag%% by my custom sort.
The /* .....*/ is because if I still do not have the tags, the preview window can work without replacing the tags.
It has been working very well in FR4. Now, in FR.Net I have a problem: It deletes the SQL statement.
I mean, I enter the SQL statement, I save the report, I open it again and if I edit the SQL, it has disappeared.
I am using a Firebird connection.
Is there a solution?
Comments
I was not able to reproduce this. Do you have a step-by-step instructions?
For example:
1-Enter
SELECT * FROM Customers
WHERE Id=5
as sql
2-Save the report and close the designer
3-Open the designer and load the report
4-If you go to see the sql, it is empty.
If I enter "SELECT * FROM Customers WHERE Id=5", in one line, it works fine.
- start the report designer;
- make a new report;
- select "Data/Add Data Source..." menu item;
- add new Firebird connection to the demo database which comes with firebird (initial catalog="C:\Program Files\Firebird\Firebird_2_1\examples\empbuild\EMPLOYEE.FDB";user id=sysdba;password=masterkey);
- press "Add SQL query" button;
- write the following SQL:
select * from employee
where emp_no > 20
- finish the data wizard;
- save the report;
- close the designer;
- open the designer;
- load the report;
- switch to the Data window, select the table and press Edit;
- the query text is here.