Dynamic filter or where clause in a source?

Support Queries Astera Centerprise
1

When using a database table as a source, one can enter a where clause. Is that possible that we can pass a where string into it by chance, making it dynamic?

Alternatively, is there a way to have dynamic filtering? I tried creating a filter and returning a string in the expression which would mimic as if I had created the filter in the expression box. But this long shot did not work as it expects the filter result to be a Boolean.

Now, a client can currently create a filter for their data in any field. I thought about a Sub Flow, but I am not sure how I could get around having to pass every field for every record into a custom method for parsing to see if it needs to be filtered or not.

3

In addition, I have been trying to use the SQLQuerySource object to try and get a dynamic filter. I notice the documentation says it can use $ parameter information, I added a parameter to the DataFlow named DealerGlobalFilter with an item in it named DealerID.

In the SQL Query window, I entered the query
"select * from ArkonaStage where DealerID = $DealerGlobalFilter.DealerID
but it gives me the following error when I attempt to click the ‘Next’ button:

Exception Message: Error running SQL statement <select * from ArkonaStage where DealerID = $DealerGlobalFilter.DealerID> to build layout. Invalid pseudocolumn “$DealerGlobalFilter”.

Exception Class Name: Astera.Transfer.TransferException

Product Version: 5.1.283.1
OS Info: Microsoft Windows NT 6.1.7600.0

Stacktrace: at Astera.Transfer.UiSupport.SqlQueryLayoutBuider.Build()

  • at Astera.Transfer.Wizards.WizardPageDbLayoutSource.BuildSqlLayout()*
  • at Astera.Transfer.Wizards.WizardPageDbLayoutSource.BuildLayout()*
  • at Astera.Transfer.Wizards.WizardPageLayout.BuildLayoutThread()*

Exception Message: Invalid pseudocolumn “$DealerGlobalFilter”.

Exception Class Name: System.Data.SqlClient.SqlException

Product Version: 5.1.283.1
OS Info: Microsoft Windows NT 6.1.7600.0

Stacktrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable)
at Astera.Transfer.UiSupport.SqlQueryLayoutBuider.Build()

4

Hi,

A couple of things here:

  1. We’ve changed the syntax slightly, but it has not been updated in the documentation yet. The syntax is now $(param). Note the parentheses.

  2. Even if you go to change this, the layout building will still fail as the resolution of the parameter only happens at runtime.

The only way to set this is to edit the XML directly. This is the only work-around at the moment for this known issue.

We are changing the UI so you can do this at design time as well as run time.
This issue was related to older builds and is now fixed in the newest builds of Centerprise.