SQL logic to Aurelius logic

BIZ TMS Aurelius
1

Hello,

I wonder if you can assist(again) - thank you.
Is their a way of putting the below SQL script into Aurelius?
A sample would be much appreciated.

DECLARE @FILTER1 AS INTEGER = 1006
DECLARE @FILTER2 AS INTEGER = 2
DECLARE @FILTER3 AS INTEGER = 0
DECLARE @FILTER4 AS VARCHAR(30) = ''

SELECT *
FROM MYTABLE
WHERE FIELD_A > 0
AND (ISNUMERIC(@FILTER1) > 0 AND FIELD_A = @FILTER1)
AND (ISNUMERIC(@FILTER2) > 0 AND FIELD_B = @FILTER2)
AND (FIELD_C = @FILTER3)
AND (CHAR(@FILTER4) <> '' AND FIELD_B = @FILTER4)

Thank you.

2

Why do you need such complex SQL instead of just putting the "FilterX" parameters directly in the SQL?

3

Hello Wagner,

Thank you for your reply.

I must apologise, my explanation was not detailed enough.
I have a Web Application that has 3 integer filters (ignore the 4th)

The filters may be used as a mix and match or none at all (retrieve all records)
If a filter = 0 then I do not want it to be part of the script

Example, Filter 1 = Order No
Filter 2 = Customer No
Filter 3 - Status code

Filter 1 & 3 are left empty (value = 0)
Filter 2 = 5

I send all 3 filters in the Call to to XDATA Server. (0,5,0)
I now want the "Find" to only search for all records with Cust no = 5, ignoring filters 1 & 2, this is what my SQL script is doing.

I could create this by writing 7 different variations of the "Find" with different Filter combinations.
I was just wondering id the Aurelius can als0 handle this is a single "Find" function.

Hope that make more sense.

Many thanks.

4

Hi,
I guess you need to write a custom XData service interface for that, where you can do

var
  MyCriteria: TCriteria<TMyEntity>;
begin
  MyCriteria := Manager.Find<TMyEntity>.Where(Linq.Gt('FieldA', 0));
  if AFilter1>0 then
    MyCriteria.Add(Linq.Eq('FieldA', AField1));
  {...and so on...}
  Result := MyCriteria.List;
end;

HTH

1 Like
5

Yes, that's a good approach.

1 Like