Page 1 of 1
SQL Multiple LIKE statements
Posted: Wed 21 Sep 2016 01:55
by markdelphi
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?
Re: SQL Multiple LIKE statements
Posted: Wed 21 Sep 2016 06:31
by AlexP
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
Re: SQL Multiple LIKE statements
Posted: Thu 22 Sep 2016 00:18
by markdelphi
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.
Re: SQL Multiple LIKE statements
Posted: Thu 22 Sep 2016 08:48
by AudioPat
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 + '%')));
Re: SQL Multiple LIKE statements
Posted: Thu 22 Sep 2016 13:48
by markdelphi
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%%''))'
Re: SQL Multiple LIKE statements
Posted: Tue 01 Nov 2016 08:52
by AlexP
Please specify, which EntityProvider do you use to connect with Firebird?