Re: SQL question - why doesn't WHERE clause know about renamed fields?

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.