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.
NOTE: Since this post was written, LiveCompare has been changed to use an embedded SQL engine instead of an external SQL Server database. Please see the comments below for how to work with dates in LiveCompare 2.5.1 and up.
For LiveCompare 2.5.0 and below:
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.
CONVERT(NVARCHAR(8), GETDATE(), 112)
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.
I've used this feature with a customer that wanted to keep an permanent record of their SAP performance history data. SAP was configured to keep the last three months only.
I created a template for them that's scheduled to run on the 1st of every month. The workflow uses the GETDATE() T-SQL function to generate the correct StartDate and EndDate parameters for the Get Performance History Data action.
The end results is a permanent archive of their SAP performance history data that now holds more than 12 months of very useful data.
A customer had the following question:
Can we have the Start date and End date parameters (linked to a Get
Performance History action) dynamically populate the Start date to
yesterday and the End date to today (scheduled to run everyday)?
Is there a statement for today's date minus one?
For Today's date, in the XSQL action, no need to edit the "Options" and the statement is:
CREATE TABLE Result (String nvarchar(8) NULL)
INSERT INTO Result VALUES (CONVERT(VARCHAR(8), GETDATE(), 112))
For Yesterday's date use:
INSERT INTO Result VALUES (CONVERT(VARCHAR(8),DATEADD(day, -1, getdate()),112))
The Results from these actions are to be used as "intables" for the Convert Data action and the "Outstrings" can be used as Start and End Date parameters for the Get Performance History Data action.
Hi I have question regarding inserting data into a SQL table.
I have a workflow that makes a series of transformations and in the end it outputs the data into an Access Report. I want to output the data into a SQL table.
Does someone knows if there's a component or a piece of workflow that already do this?
@Pedro - you have several options for writing a dataset into a table in SQL Server. First you could set up the table as an External Data Source and then use the Write External Data Source to copy the output dataset into the SQL Server table. Assuming the table is in a database on the same SQL server as LiveCompare then you could also use the Execute SQL Action and write an INSERT statement like this:
INSERT INTO [Name Of Database]..[Name of Table]
SELECT * FROM Source1
Note the two periods '..' between the name of the database and the name of the table that you want to populate.
For LiveCompare Versions < 2.1, use SQLite based statements (www.sqlite.org/search)
Create table Result as select date('now')
Note: still no need to edit the "Options" parameter.
In LiveCompare 2.5.1 and up, the SQL statement for inserting current date in selection criteria is like this:
INSERT INTO Result VALUES ('ENDDA', 'LT', strftime('%Y%m%d', date('now')), '', 'I')
And it's easy to get dates that are offset from today's date. For instance, to get 28 days ago, use:
INSERT INTO Result VALUES ('ENDDA', 'LT', strftime('%Y%m%d', date('now', '-28 days')), '', 'I')
To get the beginning of the month, use:
INSERT INTO Result VALUES ('ENDDA', 'LT', strftime('%Y%m%d', date('now', 'start of month')), '', 'I')