I mentioned in last week's post Change date from one format to another using SQL that when I converted the alphanumeric value '022195' using SQL's timestamp_format function with the format 'MMDDYY' the result was the date '02212095', which was not what I expected.
I want to thank Chris Ringer, Birgitta Hauser, and R Flagler for the solution.
By using 'YY' I stated that the year was in the current century, therefore '95' was converted to '2095'.
If I wanted to use the standard IBM date "windowing" I should use 'RR'. This would give me '1995', which is what I wanted.
Using the same data as I did in the previous post if I change my SQL statement to:
select char(date(timestamp_format(alpha,'MMDDRR')),iso)
as converted_date
from testfile
|
Then the output would be as follows:
| RECORD | ALPHA | CONVERTED_DATE |
| 1 | 122514 | 2014-12-25 |
| 2 | 022195 | 1995-02-21 |
| 3 | ++++++++++ | |
| 4 | 010201 | 2001-01-02 |
| 5 | 555555 | ++++++++++ |
| 6 | 300310 | ++++++++++ |
This article was written for IBM i 7.2, and it should work with earlier releases too.
And I probably learned that from Birgitta. :o)
ReplyDeleteChris Ringer
i really dont get it, People want to say that interactive SQL is better, might be true, but that is only good for mass data updating. The different in a HLL, you still have to fetch which is a another read in batch. so to me i rather keep using the read, and dont have to do the different compiles. And the creating of tables, i rather deal with DDS instead of DLL. Without all the create table, worst then the old I spec, before the dds world made a wonderful birth!
ReplyDeleteThe native chain operator in RPG (ou read with key in cobol) for retrieve one to one the records is good practice. But for processing multiple records the SQL is better only because the SQL has the buffer size greater than the RPG.
DeleteYou can see this with WRKJOB command and option 14 "Open file".
To copy a PF with 40^6 of records, with the CPYF command uses 10 minutes and SQL one minute only.
The DLL is a langage for multi-platform. Further, the DDL allows to use types of data that do not exists in DDS.
i just dont see that sql is better than old fashion read/chain. cpyf might slow which is out side a compile program. I was saying the read - COBOL/RPG if faster than SQL FETCH READ inside a compile program. ok?
DeleteThe difference between using native I/O (READ/CHAIN,etc) vs SQL is flexibility as much as anything. You can change what order the data is sorted by virtue of SQL vs having to use OPNQRYF or a separate LF. You can have a subfile displayed where the user can select to sort by date rather than by description vs cost for example.
ReplyDeleteThe speed of READ, etc may be faster than SQL when working with small sets of data - but I suspect that SQL is faster if working with lots of data.
In addition, SQL is easy to be more granular with the data - want records with a specific date range? Want to allow the user to search for records containing text they specify? No problem - can all be done with very few lines of code in SQL to build the SQL statement - the actual code of reading and displaying the results is precisely the same - and SQL can be much much much faster than traditional I/O.
And with SQL you can avoid change file structure and not encounter level check messages - if what you change isn't required in the program. This is good if you have a file that is used by 50 programs but only 3 use the updated or new fields. No need to recompile all 50 programs - just the 3 that are effected.
I'm not sure what the reference to CPYF has anything to do with use of SQL in RPG program...