An SQL subselect picks records based on whether a value appears in another select -- a simple example would be: select * from orders where partno in (select partno from outofstock).
My master file has two keys so to do a subselect I have to to combine them. Key1 is 9s0, key2 is 3s0 so I have been selecting like this:
select * from master where key1*1000+key2 in (select k1*1000+k2 from
billfile)
This is very slow, compared to an inner join.
I recently found that if I converted the combined key to a string, response is as fast as an inner join (apparently the iSeries is much better with characters than math):
select * from master where cast(key1 as char(9))||cast(key2 as char(3))in (select cast(k1 as char(9))||cast(k2 as char(3)) from billfile) The || is string concatenation in SQL; cast() transforms one data type to another.
==================================
MORE INFORMATION ON THIS TOPIC
==================================
The Best Web Links: tips, tutorials and more.
Visit the ITKnowledge Exchange and get answers to your developing questions fast.
Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.
