SQL Multiple LIKE statements

SQL Multiple LIKE statements

Postby markdelphi » Wed 21 Sep 2016 01:55

Hi, I'm trying to make a consultation with several Contains as follows:

Code: Select all
(DetInv.IdInventario = 32)  and (DetInv.Descripcion.Contains(''%PANT%''))  and (DetInv.Marca.Contains(''%EVIL%''))


Error

'Expression compilation error.

From(DetInv).Where().Select('*')


[TQueryConverter]

Invalid node type
{ 0 }'.
Process InvSiscab.exe (11624)

But I make a mistake, is there any way?
markdelphi
 
Posts: 16
Joined: Sat 08 Sep 2012 19:43

Re: SQL Multiple LIKE statements

Postby AlexP » Wed 21 Sep 2016 06:31

Hello,

We can't reproduce the issue. Please specify the database you are working with, the exact versions of EntityDAC and IDE, and also provide the full text of LINQ query
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: SQL Multiple LIKE statements

Postby markdelphi » Thu 22 Sep 2016 00:18

Hello
I am using Delphi Seattle with EntityDAC 1.4.8 and Firebird 2.1.3

I have to build a query with multiple fields but depends on the user to choose the fields by which you want to filter, then as I do not know what are the fields need to build the query at runtime. I was doing as follows:
Code: Select all
Condicion := '(DetInv.IdInventario = ' + IntToStr(FID_Inventario) + ') ';
for i := 0 to cboCampo.Properties.Items.Count -1 do
begin
  Titulo := cboCampo.Properties.Items.Strings[i];
  Campo :=  TString(cboCampo.Properties.Items.Objects[i]).Str;
  if FCampoValor.TryGetValue(Titulo,Valor) then
     Condicion := Condicion + ' and (DetInv.' +  Campo + '.Contains(' + QuotedStr('%' + Valor.ValorReal + '%') + ')) '
end;
Query := Linq.From('DetInv').Where(Condicion).Select;
Lista :=  Context.GetEntities<TDetInv>(Query); 
for DetInv in Lista do
  begin
   Nodo := treeProductos.FindNodeByText(DetInv.Depto,clmDepto);
   if Nodo = nil then
     Nodo := AddNodeEncabezado(DetInv.Depto);
   AgregaProducto(DetInv,Nodo);
end;



But I make the mistake that I mentioned

I had to do it follows that although it is not good is the only solution I found.
Code: Select all
procedure TfrmInventario.AplicaFiltro(ATipoCombo: TTipoComboFiltro);

function rtnCondicion : TExpression;
  var
   i : integer;
   Campo,Titulo,Condicion : string;
   Valor : TValoresCampo;
   DI : IDetInvExpression;
   ExTemp : TExpression;
  begin
   with dmInventario do
    begin
     DI := Context.DetInv;
     ExTemp := TExpression.Create(etVariable);
     ExTemp := (DI.IdInventario = 32);
     for i := 0 to cboCampo.Properties.Items.Count -1 do
       begin
        Titulo := cboCampo.Properties.Items.Strings[i];
        Campo :=  TString(cboCampo.Properties.Items.Objects[i]).Str;
        if FCampoValor.TryGetValue(Titulo,Valor) then
         begin
          if Campo = 'Descripcion' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Descripcion.Contains(Condicion)));
           end
          else if Campo = 'Marca' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Marca.Contains(Condicion)));
           end
          else if Campo = 'Tipo' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Tipo.Contains(Condicion)));
           end
          else if Campo = 'Color' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Color.Contains(Condicion)));
           end
          else if Campo = 'Talla' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Talla.Contains(Condicion)));
           end
          else if Campo = 'Modelo' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Modelo.Contains(Condicion)));
           end
          else if Campo = 'Depto' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Depto.Contains(Condicion)));
           end
          else if Campo = 'Depto' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Depto.Contains(Condicion)));
           end
          else if Campo = 'Codigo' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Codigo.Contains(Condicion)));
           end
          else if Campo = 'Almacen' then
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.IdCatMulti = Valor.ValorReal))
          else if Campo = 'Ubicacion' then
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Ubicacion = Valor.ValorReal));

         end;
       end;
      Result := ExTemp;
    end;
  end;

var
Valor : TValoresCampo;
Query : ILinqQueryable;
DI : IDetInvExpression;
DetInv : TDetInv;
Lista: IEntityEnumerable<TDetInv>;
Nodo : TcxTreeListNode;
Campo : string;
begin
if ATipoCombo = tcfEditor then
  begin
   Valor.ValorReal := cboFiltro.Text;
   Valor.ValorMuestra := cboFiltro.Text;
  end
else
  begin
   Valor.ValorReal := IntToStr(Integer(cboFiltro.Properties.Items.Objects[cboFiltro.ItemIndex]));
   Valor.ValorMuestra := cboFiltro.Properties.Items.Strings[cboFiltro.ItemIndex];
  end;
if (Valor.ValorReal = '') then
  Valor.ValorReal := Null;
FCampoValor.AddOrSetValue(cboCampo.Properties.Items.Strings[cboCampo.ItemIndex],Valor);

chklstFiltro.Items.Clear;
for Campo in FCampoValor.Keys do
  begin
   if FCampoValor.Items[Campo].ValorReal <> Null then
    begin
     with chklstFiltro.Items.Add do
      begin
       Checked := True;
       Text := Campo +  ' CONTIENE '  + FCampoValor.Items[Campo].ValorMuestra;
       ItemObject := TString.Create(Campo);
      end;
    end;
  end;
advpnlFiltro.Visible := True;
with dmInventario do
  begin
   try
     treeProductos.Clear;
     DI := Context.DetInv;
     Query := Linq.From('DetInv').Where(rtnCondicion).Select;
     Lista :=  Context.GetEntities<TDetInv>(Query);
     for DetInv in Lista do
      begin
       Nodo := treeProductos.FindNodeByText(DetInv.Depto,clmDepto);
       if Nodo = nil then
         Nodo := AddNodeEncabezado(DetInv.Depto);
       AgregaProducto(DetInv,Nodo);
      end;
   Finally

   end
  end;
end;



I also wish there was more documentation there are many things to be guessing how they work.
markdelphi
 
Posts: 16
Joined: Sat 08 Sep 2012 19:43

Re: SQL Multiple LIKE statements

Postby AudioPat » Thu 22 Sep 2016 08:48

For SQL Server:

Code: Select all
((Kernel.Database.UnitRadioDataModel.Artist.Name.Contains('%' + value.Text + '%')) and (Kernel.Database.UnitRadioDataModel.Media.Title.Contains('%' + value.Text + '%')) and (Kernel.Database.UnitRadioDataModel.Media.ItemCode.Contains('%' + valueText + '%')));
AudioPat
 
Posts: 10
Joined: Fri 19 Jun 2015 15:47

Re: SQL Multiple LIKE statements

Postby markdelphi » Thu 22 Sep 2016 13:48

For Firebird 2.1.3

Two conditions works but if I put a third no longer works.

works
Code: Select all
'(DetInv.IdInventario = 32) and (DetInv.Descripcion.Contains(''%PANT%''))'


It does not work
Code: Select all
'(DetInv.IdInventario = 32) and (DetInv.Descripcion.Contains(''%PANT%'')) and (DetInv.Marca.Contains(''%EVIL%%''))'
markdelphi
 
Posts: 16
Joined: Sat 08 Sep 2012 19:43

Re: SQL Multiple LIKE statements

Postby AlexP » Tue 01 Nov 2016 08:52

Please specify, which EntityProvider do you use to connect with Firebird?
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35


Return to EntityDAC