How to iteratively filter a table in HANA?

[Former Member]

     Hello, I am working on a HANA application for doing some dynamic selects. I have a small problem that I do not know how to solve. I have a table, for example:

Table Entries = [id, Name, FirstName, Address, City, Country, Telephone, Email, Age, etc....].

     Basically, I want to do some selects on this table for filtering. Imagine the user has on the Web Interface a TextArea where he will write his/her filters, for example: "Name = John", "Country = Germany", "Address = 123 Avenue", "Telephone=0612345678" and after that , all these filters will be entered to some specific tables, for example:

Table Personal has [id, filterID, ConstraintName, ConstraintValue]

     which will contain for example [1, 100, Name, John], [2, 100, Country, Germany], [3, 100, Address, 123 Avenue] etc.

Table Professional has [id, filterID, ConstraintName, ConstraintValue]

     which will containt for example [1, 100, Telephone, 0612345678]

At the end, I just want (for now, to make sure that my code works so far) to count the number of results.

OK, so far, everything is ok, I have the right tables, filled with the right data. But I do not know how to iteratively filter my main table (Entries), based on the constraints that are in tables Personal and Professional. Here is what I have done so far:

PROCEDURE "Filter" (IN entryID BIGINT, OUT res INTEGER)

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  DEFAULT SCHEMA "ETD"

  --READS SQL DATA

  AS

BEGIN

tempView = SELECT * FROM "Entries"; --the initial values from the whole table

-- Get the entry PERSONAL filters from the Personal table and put the results in a local table type

ltt_constraints = SELECT "ConstraintName" AS "CName", "ConstraintValue" AS "CValue" FROM "Personal" WHERE "entryID.id" = :entryID;

BEGIN

  DECLARE CURSOR c FOR SELECT "CName", "CValue" FROM :ltt_constraints;

  FOR c_row as c DO

  tempView  = SELECT * FROM :tempView WHERE c_row."CName" = c_row."CValue";

    END FOR;

END;

-- Get the entry PROFESSIONAL filters from the Personal table and put the results in a local table type

ltt_constraints = SELECT "ConstraintName" AS "CName", "ConstraintValue" AS "CValue" FROM "Professional" WHERE "entryID.id" = :entryID;

BEGIN

  DECLARE CURSOR c FOR SELECT "CName", "CValue" FROM :ltt_constraints;

  FOR c_row as c DO

  tempView  = SELECT * FROM :tempView WHERE c_row."CName" = c_row."CValue";

    END FOR;

END;

SELECT COUNT(*) INTO res FROM :tempView;

END;

[lbreddemann · Accepted solution]

Sorry but all this seems overly complicated and a sure receipt for slowness...

Alternatively you could select the different constraints into sub-sets and apply them to your base table e.g. via IN/NOT IN/EXISTS...

Or you could try and use the APPLY_FILTER function.

- Lars

[Former Member · Accepted solution]

Thank you Lars. Actually, I was looking for something exactly like APPLY_FILTER. Cheers!

[Former Member · Accepted solution]

I have another question though! Could you please tell me if it is possible to apply this function iteratively? I mean something like:

lv_constraint = '';

BEGIN

  DECLARE CURSOR c FOR SELECT "CName", "CValue" FROM :ltt_constraints;

  FOR c_row as c DO

  lv_constraint = c_row."CName" || '=' || c_row."CValue";

tempView = APPLY_FILTER(:tempView,:lv_constraint) ;

    END FOR;

END;