SQL query with Current Date in As400

aozdamar
aozdamar used Ask the Experts™
on
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
SELECT                                  
-- Columns                              
      xxxxxx, yyyyy, zzzzzz          
-- Tables                               
      FROM AAAA/BBBBB A          
-- Row Selection                        
      WHERE (((qqqqqq  = '101')      
                OR (qqqqqq  = '501'))
           AND (Date  = DateNow))

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Funny formats, often numeric, are common on the AS/400 / DB2, I'm afraid.  So you're going to have some formatting fun.

About 3 solutions to my particular format situation (for one customer) are at http://www.experts-exchange.com/Database/DB2/Q_22987515.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.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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.
Gary PattersonVP Technology / Senior Consultant

Commented:
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!).
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Theo KouwenhovenApplication Consultant

Commented:
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

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                                                      

Open in new window

Theo KouwenhovenApplication Consultant

Commented:

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
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
If you take murphey2 suggestion, you can then use translate to strip out the hyphens (-).  
TRANSLATE(CHAR(current DATE, ISO), '-', '')

Open in new window

Theo KouwenhovenApplication Consultant

Commented:
Hi aozdamar,

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
),
 
'-'
,
 
''
)
)

Open in new window

or

SELECT 
xxxxxx
,
 yyyyy
,
 zzzzzz 
FROM AAAA
/
BBBBB           

                        
  WHERE 
(
qqqqqq  
=
 
'101'
)
 OR 
(
qqqqqq  
=
 
'501'
)

    AND 

char(Date)
 
=
 
TRANSLATE
(
CHAR
(
current DATE
,
 ISO
),
 
'-'
,
 
''
)
)

Regards,
Murph

Open in new window

Theo KouwenhovenApplication Consultant

Commented:
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



Author

Commented:
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), '-', ''))
Theo KouwenhovenApplication Consultant

Commented:
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)




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     

Open in new window

Author

Commented:
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...
Theo KouwenhovenApplication Consultant

Commented:

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




&nbsp;

Author

Commented:
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
Application Consultant
Commented:
Hi Altug,

Attached you find a dtf file (data transfer from as/400).
rename it from TXT, back to DTF (EE doesn't allow DTF extention).
replace the following lines by your own choice.
HostName=HOSTNAME
HostFile=MYLIB/MYFILE
[Select=FIELD1,FIELD2,FIELD3
Where=(FIELD4 = 20080808)

To start the transfer, type in a dox box:
RTOPCB + the path/filename where the RTF file is located.
This example makes a file c:\DATA400.CSV.

You can also make your own DTF file from scratch with the file transfer utility:
To start the transfer wizzard, type in a dosbox : CWBTF, fill all te fields and save it.
After playing around with this tool, you will be used to it within 15 minutes.

How to make the date today's date... Hmmm yes wow.....you can't with this tool, I did some programs with DTF files and allways let some application in VB or even VBA write the DTF file for me, or fill in the where line only, you can even ECHO it from a dos batch file:)

Regards,
Murph

&nbsp;
&nbsp;
transf.txt

Author

Commented:
I don't know if I will be able to do this but I give you the points for the effort.

B.Regards,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial