External Oracle DB/ Aggregate or SQL filter issue

We have an external Oracle database connection to query data from an external application in Outsystems.   The data connection works as expected, however I've been struggling to use the entity filters to return the desired date.

For example, we have a user field that allows the user to select an activityID.   That value is a text string stored in a variable 'SelectedActivity'

In my aggregate I have the filter below.   I want to return all records if the variable is null/blank and if it is populated return the corresponding RowID.    It works as expected if the variable is populated but I cannot figure out any way to get all rows when that variable is blank.   Any ideas?


SyntaxEditor Code Snippet

If(SelectedActivity = "", True, SelectedActivity = PIMS_ACTIVITIES_MV.ROW_ID)

Hi Josh,

Are you trying to make an IN in aggregate ?

E.g.: 

PIMS_ACTIVITIES_MV.ROW_ID IN (1,2,3)

do this is not possible yet. But, you can use SQLQuery instead of Aggregate.


if not, if your parameter only has one result, it might work like this

SelectedActivity = "" or PIMS_ACTIVITIES_MV.ROW_ID = SelectedActivity


I don't know if I understand your problem,


Regards

Pedro Costa wrote:

Hi Josh,

Are you trying to make an IN in aggregate ?

E.g.: 

PIMS_ACTIVITIES_MV.ROW_ID IN (1,2,3)

do this is not possible yet. But, you can use SQLQuery instead of Aggregate.


if not, if your parameter only has one result, it might work like this

SelectedActivity = "" or PIMS_ACTIVITIES_MV.ROW_ID = SelectedActivity


I don't know if I understand your problem,


Regards

No, it should be very basic but for some reason it doesn't work.   Let's say a user enter "abc123" in the SelectedActivity field.   I would expect the aggregate to only return that record, but if the user enters nothing in the SelectedActivity field I would want the aggregate to return all activities.


I have done this dozens of times using normal OS entities, but these entities are created via a connection to an Oracle MV and for whatever reason no matter what I try I cannot get the aggregate to return all activities if that variable is empty.


What is the datatype of attribute ?

Does the same thing happen using AdvanvecedQuery?

The Oracle elements are VarChar and yes I'm struggling to get it to work using Advanced SQL.   As a work around I'm using the expand inline on the input parameter for the advanced SQL and just building my string as needed in my screen action.


It's very odd... but if I create the query string and pass that into advanced SQL it works as expected.   So I ended up doing below.   So if the SelectedActivity IS blank the query string is nothing and that is giving me back results.  It's clunky and I don't like it but I spent all morning spinning my wheels so I had to come up with something.

SyntaxEditor Code Snippet

If(SelectedActivity <> "", " AND {ACTIVITIES_MV}.[ROW_ID] = "+"'"+SelectedActivity + "' OR {ACTIVITIES_MV}.[PARENT_ROW_ID] = " + "'" + SelectedActivity + "'","")