SQL: Select With WHERE clause

Data Abstract
1

Hi

I have some problems with this code, the Problem is that it dos not filter.

sql_string_default := 'SELECT * FROM MEMBER AS CABINET_G '+
‘WHERE (CABINET_G = ‘‘P’’) or (CABINET_G = ‘‘VP’’) or (CABINET_G = ‘‘CH’’) or (CABINET_G = ‘‘S’’) or (CABINET_G = ‘‘T’’)’;
ClientDataModule.RemoteDataAdapter.FillWithDASql(DM_reports.tbl_rep_member,sql_string_default);

Did also read this:
http://wiki.remobjects.com/wiki/DA_SQL

– boolean logic, including AND, OR
SELECT * FROM customers AS c
WHERE (cust.id = 2) or (cust.id = 4) or (cust.id = 3)
tank you for any Tipps

2

you are trying to execute SQL like:

select * from table 
where (table = "p") or (table = "vp") ...

looks like you need to specify field in where clause:

WHERE (CABINET_G.fld1 = 'P')

also DA_SQL works only with DAN servers or Relativity

3

Thank you for your fast Reply.
I am using the Relativity Server.

Did what you mentiond but with no change:

sql_string_default := 'SELECT * FROM MEMBER '+
'WHERE (MEMBER.CABINET_G = ‘‘P’’ or MEMBER.CABINET_G = ‘‘VP’’ '+
'or MEMBER.CABINET_G = ‘‘CH’’ or MEMBER.CABINET_G = ‘‘S’’ or MEMBER.CABINET_G = ‘‘T’’) '+
‘AND (MEMBER.STAT_DEL = 0)’;
.

4

Hello

What do you mean by “it does not filter”? Did you get any exceptions or just some not expected data?

5

Hi
I get all data from the Table.
Like if i would do this:

SELECT * FROM MEMBER

6

I used FibPlus bevore and there it worked like this:

with DM_reports.member do
begin
close;
SelectSQL.Clear;
SelectSQL.Add ('SELECT * FROM MEMBER '+
'WHERE ((C_NO = ?C_NO) '+
‘AND (STAT_DEL CONTAINING ‘+‘0’+’)’+
'AND CABINET_G IN ( ‘‘P’’, ‘‘VP’’, ‘‘CH’’, ‘‘S’’, ‘‘T’’ )) ');
Open;
end;

7

These queries aren’t identical. Latter one (ie FibPlus one) contains one more condition. So depending on the actual data the DA SQL query could return entire table while FibOne results in just a couple of rows.

Could you upload to support@ a testcase database with like a dozen of rows from the MEMBER table?

8

These queries aren’t identical.

Do you mean this:
'WHERE ((C_NO = ?C_NO) '+

I did not finde a way of doing that nor i found a Forum entry or Howto.
Maybe i missed that…

Just for Info: This was my solution before:

if camp_ident_part = ‘’ then
begin
sql_string_rep_camp := ‘SELECT * FROM CAMPS WHERE ( CITY IS NOT NULL ) ORDER BY CITY’;
end
else
begin
sql_string_rep_camp := ‘SELECT * FROM CAMPS WHERE ( CITY IS NOT NULL AND C_NO = ‘’’ + camp_ident_part + ‘’‘) ORDER BY CITY’;
end;
// FibPLus Dataset setup start:
with DM_reports.camps do
begin
close;
SelectSQL.Clear;
SelectSQL.Add (sql_string_rep_camp);
Open;
end;
//
with DM_reports.member do
begin
close;
SelectSQL.Clear;
SelectSQL.Add (‘SELECT * FROM MEMBER WHERE C_NO = ?C_NO AND (STAT_DEL CONTAINING ‘+‘0’+’) ORDER BY F_NAME’);
Open;
end;
// FibPLus Dataset setup end:
with DM_reports.frxReport do
begin
Variables.LoadFromFile(report_dir+‘/report_var.fd3’);
LoadFromFile(report_dir+‘/report_member.fr3’);
DM_reports.set_variables(Sender);
PreviewOptions.OutlineVisible := true;
PrepareReport;
ShowPreparedReport;
end;

And this is my test now:

if camp_ident_part = ‘’ then
begin
sql_string_rep_camp := ‘SELECT * FROM CAMPS WHERE ( CITY IS NOT NULL ) ORDER BY CITY’;
end
else
begin
sql_string_rep_camp := ‘SELECT * FROM CAMPS WHERE ( CITY IS NOT NULL AND C_NO = ‘’’ + camp_ident_part + ‘’‘) ORDER BY CITY’;
end;

sql_string_default := ‘SELECT * FROM MEMBER WHERE (STAT_DEL = ‘+’’‘0’‘’+‘) ORDER BY F_NAME’;

ClientDataModule.RemoteDataAdapter.FillWithDASql(DM_reports.tbl_camps,sql_string_rep_camp);
ClientDataModule.RemoteDataAdapter.FillWithDASql(DM_reports.tbl_rep_member,sql_string_default);

with DM_reports.frxReport do
begin
Variables.LoadFromFile(report_dir+‘/report_var.fd3’);
LoadFromFile(report_dir+‘/report_member.fr3’);
DM_reports.set_variables(Sender);
PreviewOptions.OutlineVisible := true;
PrepareReport;
ShowPreparedReport;
end;
end;

9

you can use parameters with DA SQL as:

//  par: DataParameterArray;
//  dp: DataParameter;

      par := DataParameterArray.Create;
      dp := par.Add;
      dp.Name := UTF8Encode('param1');
      dp.Value := 1;
      lRemoteDataAdapter.FillWithDASql(lTable,DASQL1, par);
10

Hi again

Sorry but i am still struggling.

Found a solution that works now:

begin
lDynWhere.Expression := lDynWhere.NewBinaryExpression(
lDynWhere.NewBinaryExpression(
lDynWhere.NewBinaryExpression(‘’,‘STAT_DEL’,dboLike,‘0’),
lDynWhere.NewBinaryExpression(‘’,‘STAT_DEL’,dboLike,‘0’),
dboAnd
),
lDynWhere.NewBinaryExpression(
lDynWhere.NewField(‘’,‘CABINET_G’),
lDynWhere.NewList(
[lDynWhere.NewConstant(‘P’),
lDynWhere.NewConstant(‘VP’),
lDynWhere.NewConstant(‘S’),
lDynWhere.NewConstant(‘T’),
lDynWhere.NewConstant(‘CH’)]),
dboIn),
dboAnd);
end;

It is still unklear to me wy the SQL Select with the WHERE is not working…

11

why you use

lDynWhere.NewBinaryExpression('','STAT_DEL',dboLike,'0'),

twice?

12

If i do it like this, i get the Error

There is no overloaded version of ‘NewBinaryExpression’ that can be called with these arguments

begin
lDynWhere.Expression := lDynWhere.NewBinaryExpression(
lDynWhere.NewBinaryExpression(
lDynWhere.NewBinaryExpression(‘’,‘STAT_DEL’,dboLike,‘0’),
dboAnd
),
lDynWhere.NewBinaryExpression(
lDynWhere.NewField(‘’,‘CABINET_G’),
lDynWhere.NewList(
[lDynWhere.NewConstant(‘P’),
lDynWhere.NewConstant(‘VP’),
lDynWhere.NewConstant(‘S’),
lDynWhere.NewConstant(‘T’),
lDynWhere.NewConstant(‘CH’)]),
dboIn),
dboAnd);
end;

13

how about ?

var
  e1,e2: TDAWhereExpression;
begin
   e1 := lDynWhere.NewBinaryExpression('','STAT_DEL',dboLike,'0');
   e2 := lDynWhere.NewBinaryExpression(
             lDynWhere.NewField('','CABINET_G'),
             lDynWhere.NewList(
               [lDynWhere.NewConstant('P'),
                lDynWhere.NewConstant('VP'),
                lDynWhere.NewConstant('S'),
                lDynWhere.NewConstant('T'),
                lDynWhere.NewConstant('CH')]),
            dboIn);
   lDynWhere.Expression := lDynWhere.NewBinaryExpression(e1,e2, dboAnd);
end;
14

Great, it just works. :wink:
Thank you for your help!

Shalom
Manfred

15

Hi again…

This is working:

begin
e1 := lDynWhere.NewBinaryExpression(‘’,‘STAT_DEL’,dboEqual,‘0’);
e2 := lDynWhere.NewBinaryExpression(‘’,‘GIDEON_NO’,dboGreater,‘0’);
lDynWhere.Expression := lDynWhere.NewBinaryExpression(e1,e2, dboAnd);
end;

Why is this not working?

begin
e1 := lDynWhere.NewBinaryExpression(‘’,‘STAT_DEL’,dboEqual,‘0’);
e2 := lDynWhere.NewBinaryExpression(‘’,‘GIDEON_NO’,dboGreater,‘0’);
e3 := lDynWhere.NewBinaryExpression(‘’,‘CERT_G’,dboEqual,‘False’);
lDynWhere.Expression := lDynWhere.NewBinaryExpression(e1,e2,e3, dboAnd);
end;

Did also look here: Documentation | RemObjects Software
This is also not working:

with DM_reports.tbl_rep_member, DynamicWhere do begin
Expression:=NewBinaryExpression(
NewBinaryExpression(NewField(LogicalName,‘STAT_DEL’),NewConstant(‘0’),dboEqual),
NewBinaryExpression(NewField(LogicalName,‘GIDEON_NO’),NewConstant(‘0’),dboGreater),
NewBinaryExpression(NewField(LogicalName,‘CERT_G’),NewConstant(‘False’),dboEqual),
dboAnd);
end;

greetings
Manfred

16

NewBinaryExpression accepts only 2 expressions.
If you need to use 3 expressions or more, use

lDynWhere.Expression := lDynWhere.NewBinaryExpressionList([e1,e2,e3], dboAnd);

or

e1 := lDynWhere.NewBinaryExpression('','STAT_DEL',dboEqual,'0');     
e2 := lDynWhere.NewBinaryExpression('','GIDEON_NO',dboGreater,'0');     
e4 := lDynWhere.Expression := lDynWhere.NewBinaryExpression(e1,e2, dboAnd);  
e3 := lDynWhere.NewBinaryExpression('','CERT_G',dboEqual,'False');     
lDynWhere.Expression := lDynWhere.NewBinaryExpression(e4,e3, dboAnd);
17

Thank you for your fast replay :wink:
Will try that.

Shalom
Manfred