How to get the current date in a user defined query in datamapper?

OL Connect Professional DataMapper
1

Hello,

we need to get the current date in the query of the database datamapper.
Actually the date is hard coded in the query, which looks like the following:

=SELECT * FROM [AS400].[].[].[V_PRINT_LETTERS]
WHERE DR1_DATUM_N08 =20222405
ORDER BY AT2E_KDNR, AT2E_LFDNR7

What is the best way to get the current date dynamic?

The field DR1_DATUM_N08 has the type NUMERIC(8,0).
The database is a DB2 AS/400.

Thanks for help

2

The online help explains how to do this:
PReS Connect 2021.2 User Guide - SQL Query Designer (objectiflune.com)

3

I have tried this before, but I got an error message when I do this.
In workflow I have a variable %9 with value %y%m%d.

When using the variable {automation.jobInfo.JobInfo9} in the query, I got the message:

image
image1197×800 181 KB

4

Try setting a default value for JobInfo9 so you can test it directly from the DataMapper interface. Also, you way want to try adding quotes around the value:

... WHERE DR1_DATUM_N08 = "{automation.jobInfo.JobInfo9}"
5

With and without quotes I got the same message that there was an error in the query, and in messages it shows java.lang.NullPointerException.

6

I have tested it here with various combinations and it works fine:

=SELECT * FROM {data.properties.Table} WHERE GENDER = "{automation.jobInfo.JobInfo9}"

or

=SELECT * FROM {automation.parameters.Table} WHERE FIRSTNAME = "{automation.jobInfo.JobInfo8}"

Have you set default/debug values in the DataMapper for the variables you are using in your SQL statement? And which version of Connect are you using?

7

Have you tried using the SQL from the DB2 like so:

CAST(VARCHAR_FORMAT(CURRENT TIMESTAMP, ‘YYYYDDMM’) AS NUMERIC(8,0))

It would look like his in your query:

=SELECT *
FROM [AS400].[].[].[V_PRINT_LETTERS]
WHERE DR1_DATUM_N08 = CAST(VARCHAR_FORMAT(CURRENT TIMESTAMP, ‘YYYYDDMM’) AS NUMERIC(8,0))
ORDER BY AT2E_KDNR, AT2E_LFDNR7

8

I got the following sql statement running, which is a little bit slow, but it is working.

=SELECT * FROM [AS400].[].[].[V_PRINT_LETTERS_KDBM0101]
WHERE DR1_DATUM_N08 = (SELECT MAX(DR1_DATUM_N08) FROM [].[].[D80EKTO].[V_PRINT_LETTERS_KDBM0101])
ORDER BY AT2E_KDNR, AT2E_LFDNR7

I have to take a look why the parameter from jobInfo9 is not working.

9

That query will return the row which are = to the latest date in your DB, not the date of today. So let’s say that the last entry was on a Friday and on the next Monday you run it, it will return you all the row from last Friday. (assuming no entries are done during the weekend). Is that what you expect/desire?