× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Alan,
I saw this, and had downloaded it.
however I felt that the only conversion I would ever need
would be the Dec (8,0) to date...

gerald


On 11/8/2010 2:18 PM, Alan Campin wrote:
Why reinvent the wheel? iDate available at
www.think400.dk/downloads.htmprovides support for AS400 types( number,
character, fomatted, etc) and date
to numeric. All free.

On Mon, Nov 8, 2010 at 12:19 PM, Charles Wilt<charles.wilt@xxxxxxxxx>wrote:

Well, you'd want a second UDF so you can go either direction...

num --> date or date --> num

Even with a index on hord, this requires a full table scan...
WHERE numtodate(hord) + 30 days< CURRENT_DATE

However, this can use an index on hord
WHERE hord< datetonum(CURRENT_DATE - 30 days))

In other words, if you have 100,000 records in the file and 3 of them
match your criteria, the first version will read 100,000 records to
find the 3 matching ones whereas the 2nd version will only read the 3
matching records.

It's not just UDFs, most built in functions prevent the use of an index.

HTH,
Charles

On Mon, Nov 8, 2010 at 1:54 PM, Gqcy<gmufasa01@xxxxxxxxx> wrote:
Charles,
could you please explain your example for me?
( select * from ech
where hord< datetonum(CURRENT_DATE - 30 days))

Will I need to change my UDF?



On 11/8/2010 12:41 PM, Charles Wilt wrote:
Yes it is cool...however, it's not generally a good idea.

By using a function on the file field in the WHERE clause, you force
the system to do a full table scan at worst or a full index scan at
best. Thus you lose most of the benefits of having an index.

In this scenario, it doesn't matter so much as you want to compare
numeric dates between two files. However for example, say you were
doing this:

select * from ech
where numtodate(hord) + 30 days< CURRENT_DATE

You're much better off doing it this way
select * from ech
where hord< datetonum(CURRENT_DATE - 30 days)

HTH,
Charles




On Mon, Nov 8, 2010 at 12:05 PM, Gqcy<gmufasa01@xxxxxxxxx> wrote:
Thanks all ,

I'm testing that right now:
here is what I have:

With tmpfile as (
SELECT ech.hord,
numtodate(polog.poendt) as day_entered,
numtodate(ech.hedte) as day_ordered
FROM xyzlib/pologpf AS polog
JOIN xyzlib/ech AS ech
ON polog.pokord = ech.hord
)
Select count(*) from tmpfile
where days(day_entered) + 30< days(day_ordered)


THIS IS SOOOO COOL!




--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.