As an aside: When using UDF it is not possible generating an index over an
UDF!
If a function includes more than a single statement it is a black box for
the optimizer.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Roger
Harman
Sent: Friday, 1 September 2023 20:27
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL dates and numeric columns
Or, consider a UDF for the conversions in lieu of the joins. Your SQL
statement will be a lot cleaner.
Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan
Campin
Sent: Friday, September 1, 2023 11:21 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL dates and numeric columns
As I have previously recommended create a table using DDL with fields for
Date data type, date in ccyymmdd, numeric cyymmdd, mmddyy, mmddccyy and one
for century number, year number, month number and day number. Then write a
quick and dirty program to accept a range of dates to write.
Make sure you use DDL for high speed indexes. create the indexes when
creating the table.
Now you need a date converted just join to table and get converted to any
other format.
If you do with math SQL cannot optimize.
If you need example of table I can create one for you. Just let me know.
On Fri, Sep 1, 2023, 1:41 AM Dave <dfx1@xxxxxxxxxxxxxx> wrote:
Hi,
Trying to do date operations on a physical file where the date is
coded in
3 different numeric fields Y, M and D
1 - I created a view, can I do better than this :
date(trim(char(Y))!!'-'!!trim(char(M))!!'-'!!trim(char(D))) "MyDate"
?
2 - This works in an SQL session but the RPG compiler doesn't seem to
like the ' !' character. Any ideas ?
Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
