When developing a workflow that reads data from an SAP table, LiveCompare lets you select which rows to read by means of the aptly-named Rows to Read parameter.  If you have a fixed set of selection criteria then you can edit the parameter once and be done.  But what if the selection criteria are dynamic?

No worries -- you can put the selection criteria into a table, use the Convert Data action to convert the table into a select list, then use that select list as your Rows to Read.  Typically an Execute SQL action is used to create the table, but you could also read it in from an external data source.  What's most important is that the table must have columns that correspond to the data you enter in the Select List parameter editor, namely Field, Operator, Low value, High value, I/E.  This technique is used in several of the LiveCompare workflow templates, for example in the Upgrade Analysis template U.12 - Impacted Profiles and Authorizations.

Now suppose you have some kind of auditing workflow that is run on a regular basis and you need to select rows based on today's date.  How do you get today's date into the selection criteria?

The answer again lies in using the Execute SQL action.  SQL Server provides a built-in function called GETDATE.  You can call GETDATE and put the result into the selection criteria.  In the simplest case, it just takes two SQL statements.  The first statement creates a table dataset with the appropriate set of columns that can be converted into a select list:

CREATE TABLE Result (FIELD nvarchar(20) NULL, OP nvarchar(2) NULL, LO nvarchar(40) NULL, HI nvarchar(40) NULL,IE nvarchar(1) NULL)

Then just insert selection criteria into the table.  For example, if I want to select rows where the ENDDA field has a date earlier that today, the SQL is:

INSERT INTO Result VALUES ('ENDDA', 'LT', CONVERT(NVARCHAR(8), GETDATE(), 112), '', 'I')

Of course if there are additional row selection criteria you can add more INSERT statements, e.g.

INSERT INTO Result VALUES ('KEY1', 'EQ', 'KEYVALUE1', '', 'I')

Now you may be asking, what's that CONVERT thing in there?  GETDATE returns the current date and time as a 'datetime' value.  The ENDDA field in the SAP table is an eight-character string in YYYYMMDD format.  CONVERT(NVARCHAR(8), GETDATE(), 112) says take the datetime value returned by the GETDATE function and produce an 8-character string, using format style 112.  Format style 112 means, you guessed it, YYYYMMDD.

While we're on the subject of date fields, the internal SAP date format is eight characters like YYYYMMDD, but when date values get read into LiveCompare, dashes get inserted so you end up with YYYY-MM-DD.  This makes the dates easier to read and matches the format you see if you happen to use transaction SE16 to look at table data.

However, this automatic formatting means that you have to be careful when filtering by date.  For the Rows to Read parameter on a Read SAP Table action, you should use format 112 to get YYYYMMDD.  However, if you're writing a WHERE clause in SQL and you need to select rows using a date field that's already in a table dataset, use format 23, which gives you YYYY-MM-DD instead.  The field length needs to be ten characters because of the dashes, so the CONVERT function call looks like this:

CONVERT(NVARCHAR(10), GETDATE(), 23)

There are a lot of functions available in SQL and they can be used to do some interesting things.  For example, the Audit template A.02 - Dormant users is able to compute the number of days that an SAP user account has been dormant by using DATEDIFF, CONVERT, and GETDATE.