Add criteria to SQL generated by QBE

[giga]

Hi,
I am using QBE with TOracleDataSet. I need add some criteria before I call ExecuteQBE. How can I do it? If I modify SQL.Text it is not used in this query. When I modify it in BeforeQuery then original SQL won't be restored.

For example:
In QBE user enter department_id = '8%'
I need add criteria ' and level > 1 and level < 5 '

DOA 4.0.7.1

 

[Marco Kalter]

You can modify the SQL Text before you enter QBE mode. The QBE criteria will be added to the existing where clause.

 

[giga]

Thank you, but I am not able to modify SQL Text before I enter QBE. I have detail form (filter form) with DBEdits and twenty CheckBoxes. I enter QBE mode and display this form. User can filter data using QBE (writing text in DBEdits) or check some checkboxes. When user press OK on this form, I call ExecuteQBE. But I need add criteria which depends on checkboxes to QBE generated SQL. If I want to modify sql text before QBE mode, I have to split this form to two forms.
Is there another way? For example some protected property ..

Regards Vlada

 

[Marco Kalter]

I think you can use the AfterQBE event for this:

Declaration
type TOracleDataSetEvent = procedure(Sender: TOracleDataSet) of Object;
property AfterQBE: TOracleDataSetEvent;

Description
Fires when the dataset leaves QBE mode, but before the query is executed. You can perform checks on the QBE values, modify QBE values, restore the appearance of queryable controls, and so on. When an exception is raised in this event, the dataset will remain in QBE mode.

I haven't tried it myself, but it's worth a shot.

 

[giga]

I am afraid that it is not usable for me. Because I need DIRECTLY MODIFY the generated SQL. When I change SQL.Text in AfterQBE event this changes aren't used in query.
I want to make changes into QBE generated SQL and use them in query. I am not able to do this changes by setting fields in QBE.

Thank you.

 

[Marco Kalter]

Perhaps you can set field values based on your checkboxes during QBE mode?

 

[giga]

I suppose not.
For example the criteria which I want add is:
(zp_status = 'R' and zp_level in ('Q','D','F') and exists select num from zp_users where id = :id))

:id is value entered by user into a field on FilterForm (I replace it with value when building expression string).

 

[Marco Kalter]

I did a little test with the AfterQBE event, and it seems to work fine. This is my code for the EmpDataSet of the DeptEmp demo:

procedure TMainForm.EmpDataSetAfterQBE(Sender: TOracleDataSet);
begin
  EmpDataSet.Debug := True;
  if OriginalSQL = '' then OriginalSQL := EmpDataSet.SQL.Text;
  EmpDataSet.SQL.Text := OriginalSQL + ' and e.sal > 1000';
end;

My additional criteria (e.sal > 1000) are added to the QBE criteria entered on the form.