aozdamar
asked on
SQL query with Current Date in As400
Hello experts,
I have created a query in Query manager tool that lists some fields based on a date. This query will be run everyday and the date filter should be the current date. Someway I have to compare a date field in a table with the current date. Can some one help me with this ?
I defined an expression that creates a field that for Current Date (DateNow=Current Date)
However Date field in the table is a numeric value. (ex. 20080801) and the Datenow field is like 01.08.08 so I cannot compare them. The way I am trting to compare is, Date EQ Datenow
I have created a query in Query manager tool that lists some fields based on a date. This query will be run everyday and the date filter should be the current date. Someway I have to compare a date field in a table with the current date. Can some one help me with this ?
I defined an expression that creates a field that for Current Date (DateNow=Current Date)
However Date field in the table is a numeric value. (ex. 20080801) and the Datenow field is like 01.08.08 so I cannot compare them. The way I am trting to compare is, Date EQ Datenow
SELECT
-- Columns
xxxxxx, yyyyy, zzzzzz
-- Tables
FROM AAAA/BBBBB A
-- Row Selection
WHERE (((qqqqqq = '101')
OR (qqqqqq = '501'))
AND (Date = DateNow))
Daniel, your examples are exactly what my first thoughts were as well. Another alternative to having to alter the integer values so they add up correctly is you can convert the Year(), Month(), and Day() returns to be strings and concatenate to same format like '20080101' and then just convert your Date field to string during comparison as well.
In the distant past, the AS/400 database didn't support Date, Time, and Timestamp fields. Then there was a period when the database supported them, but the legacy programming languages (RPG, CL), didn't. As a result there are a lot of legacy tables that contain dates, times, and timestamps stored in numeric format.
I still see legacy programmers that "grew up" in the "pre-date" era that create numeric fields for these data types.
It is a constant pain.
Create a set of functions to handle the particular brand of numeric -> date/time/timestamp conversions required in your system (and add them to your toolkit for the next shop with similar problems!).
I still see legacy programmers that "grew up" in the "pre-date" era that create numeric fields for these data types.
It is a constant pain.
Create a set of functions to handle the particular brand of numeric -> date/time/timestamp conversions required in your system (and add them to your toolkit for the next shop with similar problems!).
Hi aozdamar,
within SQL you have the Currendate like
Where MyDate = Current Date
But the format = YY/MM/DD
You can change that by CHAR(current DATE, ISO)
Then the result will be 2008-08-06 but you need some substringing
to get your DB-date into this format. so it's more easy to pass the date
from CL to the Management query
alter Date = DateNow into Date = &DateNow
And write a CL to retrieve today's date from the system value
See Snippet
Regards,
Murph
within SQL you have the Currendate like
Where MyDate = Current Date
But the format = YY/MM/DD
You can change that by CHAR(current DATE, ISO)
Then the result will be 2008-08-06 but you need some substringing
to get your DB-date into this format. so it's more easy to pass the date
from CL to the Management query
alter Date = DateNow into Date = &DateNow
And write a CL to retrieve today's date from the system value
See Snippet
Regards,
Murph
PGM
DCL VAR(&SYSDATE) TYPE(*CHAR) LEN(20)
DCL VAR(&DATENOW) TYPE(*CHAR) LEN(8)
RTVSYSVAL SYSVAL(QDATETIME) RTNVAR(&SYSDATE)
CHGVAR VAR(&DATENOW) VALUE(%SST(&SYSDATE 1 8))
STRQMQRY QMQRY(MyLib/MyQMQ) SETVAR((DATENOW &DATENOW))
ENDPGM
Hi Gary,
Yep your right:
I still see legacy programmers that "grew up" in the "pre-date" era that create numeric fields for these data types.
After al these years I use the numeric date fields again, after loads of troubles in diferent conversion jobs.
Don't try to learn an old monkey new tricks.
Murphey
If you take murphey2 suggestion, you can then use translate to strip out the hyphens (-).
TRANSLATE(CHAR(current DATE, ISO), '-', '')
Hi aozdamar,
So with help from mwvias1 the SQL should work like this:
So with help from mwvias1 the SQL should work like this:
SELECT
xxxxxx
,
yyyyy
,
zzzzzz
FROM AAAA
/
BBBBB
WHERE
qqqqq
in (
'101'
,
'501'
)
AND
char(Date)
=
TRANSLATE
(
CHAR
(
current DATE
,
ISO
),
'-'
,
''
)
)
orSELECT
xxxxxx
,
yyyyy
,
zzzzzz
FROM AAAA
/
BBBBB
WHERE
(
qqqqqq
=
'101'
)
OR
(
qqqqqq
=
'501'
)
AND
char(Date)
=
TRANSLATE
(
CHAR
(
current DATE
,
ISO
),
'-'
,
''
)
)
Regards,
Murph
BTW aozdamar,
"This query will be run everyday"
Can this also be Every night?
in that case non of these options will work unless you are sure the QSL is executed before 0:00 h.
If it's a part of the Nightly batch, you better take the JobDate instead of the system date,
This jobdate can be retrieved in CL by RTVJOBD.
An other option that will work is:
SELECT xxxxxx, yyyyy, zzzzzz FROM AAAA/BBBBB
WHERE (qqqqqq = '101') OR (qqqqqq = '501')
AND Date in (Select MAX(Date) FROM AAAA/BBBBB)
This allways will select the records equal to the highest (newest) date in the table, but if there was no record inserted during 1 day, the SQL will make a selection from the last date found.
Regards,
Murph
"This query will be run everyday"
Can this also be Every night?
in that case non of these options will work unless you are sure the QSL is executed before 0:00 h.
If it's a part of the Nightly batch, you better take the JobDate instead of the system date,
This jobdate can be retrieved in CL by RTVJOBD.
An other option that will work is:
SELECT xxxxxx, yyyyy, zzzzzz FROM AAAA/BBBBB
WHERE (qqqqqq = '101') OR (qqqqqq = '501')
AND Date in (Select MAX(Date) FROM AAAA/BBBBB)
This allways will select the records equal to the highest (newest) date in the table, but if there was no record inserted during 1 day, the SQL will make a selection from the last date found.
Regards,
Murph
ASKER
Hello guys,
First of all thanks so much for your suggestions. I am very new to AS400 stuff and the sql statement I have posted is taken from the a menu called SQL view in "Work with Query Manager Queries".
This query will run every 10 minutes. Because I have no idea how to set and run this query automatically in AS 400 I have created a script that runs the IBM client access and runs the query in windows as a scheduled job.
I want to try the solution that murphy2 suggested. However I don't know how. I mean should I copy and paste the below code and paste in to the screen that comes with STRSQL command ?
I might be asking a funny question but as I said I am very new to AS 400 stuff. Just not to deal with AS400 I even tried an ODBC connection with MS access and create the query there, however everytime I need to run the query it asks for username and pwd to connect to DB2...
<pre class="prettyprint">SELECT xxxxxx, yyyyy, zzzzzz FROM AAAA/BBBBB
WHERE qqqqq in ('101', '501')
AND char(Date) = TRANSLATE(CHAR(current DATE, ISO), '-', ''))</pre>or
<pre class="prettyprint">SELECT xxxxxx, yyyyy, zzzzzz FROM AAAA/BBBBB
WHERE (qqqqqq = '101') OR (qqqqqq = '501')
AND char(Date) = TRANSLATE(CHAR(current DATE, ISO), '-', ''))
First of all thanks so much for your suggestions. I am very new to AS400 stuff and the sql statement I have posted is taken from the a menu called SQL view in "Work with Query Manager Queries".
This query will run every 10 minutes. Because I have no idea how to set and run this query automatically in AS 400 I have created a script that runs the IBM client access and runs the query in windows as a scheduled job.
I want to try the solution that murphy2 suggested. However I don't know how. I mean should I copy and paste the below code and paste in to the screen that comes with STRSQL command ?
I might be asking a funny question but as I said I am very new to AS 400 stuff. Just not to deal with AS400 I even tried an ODBC connection with MS access and create the query there, however everytime I need to run the query it asks for username and pwd to connect to DB2...
<pre class="prettyprint">SELECT
WHERE qqqqq in ('101', '501')
AND char(Date) = TRANSLATE(CHAR(current DATE, ISO), '-', ''))</pre>or
<pre class="prettyprint">SELECT
WHERE (qqqqqq = '101') OR (qqqqqq = '501')
AND char(Date) = TRANSLATE(CHAR(current DATE, ISO), '-', ''))
In the case you describe, I would go for the other solution:
SELECT xxxxxx, yyyyy, zzzzzz
FROM AAAA/BBBBB
WHERE qqqqqq in ('101', '501')
AND Date = &DateNow
Put this in a source file and create the query with CRTQMQRY
then make a source member from the type CLP
and copy the next snippet into it change the 'MyLib/MyQMQ' into your query name and compile.
Now you can run the job by calling your CL program.
if you like you can start this daily by the as/400 scedular and extend the CL program with a loop so that it runs every 10 minutes until a specific time.
But please explain what the purpose is of this query, maybe there are more solutions (more easy)
SELECT xxxxxx, yyyyy, zzzzzz
FROM AAAA/BBBBB
WHERE qqqqqq in ('101', '501')
AND Date = &DateNow
Put this in a source file and create the query with CRTQMQRY
then make a source member from the type CLP
and copy the next snippet into it change the 'MyLib/MyQMQ' into your query name and compile.
Now you can run the job by calling your CL program.
if you like you can start this daily by the as/400 scedular and extend the CL program with a loop so that it runs every 10 minutes until a specific time.
But please explain what the purpose is of this query, maybe there are more solutions (more easy)
PGM
DCL VAR(&SYSDATE) TYPE(*CHAR) LEN(20)
DCL VAR(&DATENOW) TYPE(*CHAR) LEN(8)
RTVSYSVAL SYSVAL(QDATETIME) RTNVAR(&SYSDATE)
CHGVAR VAR(&DATENOW) VALUE(%SST(&SYSDATE 1 8))
STRQMQRY QMQRY(MyLib/MyQMQ) SETVAR((DATENOW &DATENOW))
ENDPGM
ASKER
This query gets the goods in data of the warehouse from our system. I will send this data to our outsource Warehouse companies ftp server. So that they will make the same entries to their system. Some kind of an EDI...
So it must be stored in a file?
Then send to the warehouse using FTP?
You FTP it from a PC or from the AS/400?
I advise to keep it on 1 system
Create a AS/400 solution that select and send it to the warehouse
-or-
Download it into a PC file every 10 minutes via
AS/400 File transfer or ODBC (I can provide you an example for both, but I advise filetransfer)
-or-
Hire me to develope the tool for you.... :)
Regards,
Murph
ASKER
Hi Murphey2,
I wish I have the opportunity to hire you :)
Download it into a PC file every 10 minutes via
AS/400 File transfer or ODBC advice sounds nice. Because I will be sending the files via a thirdparty FTP software called AutoFTP. I would like to ave a copy of the files on my windows system just incase of an error in transfer. I f you can provide an example that would be great actually.
B.regards,
Altug
I wish I have the opportunity to hire you :)
Download it into a PC file every 10 minutes via
AS/400 File transfer or ODBC advice sounds nice. Because I will be sending the files via a thirdparty FTP software called AutoFTP. I would like to ave a copy of the files on my windows system just incase of an error in transfer. I f you can provide an example that would be great actually.
B.regards,
Altug
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I don't know if I will be able to do this but I give you the points for the effort.
B.Regards,
B.Regards,
About 3 solutions to my particular format situation (for one customer) are at https://www.experts-exchange.com/questions/22987515/iSeries-DB2-CURRENT-TIMESTAMP-Decimal-14-in-SQL.html
Perhaps you can adapt one of those ideas. I do think that using CURRENT_TIMESTAMP as the basis of your calculation is probably the way to go.