Is it possible to return a list of numbers from a Sybase function?
- by ps_rs4
I'm trying to overcome a very serious performance issue in which Sybase refuses to use the primary key index on a large table because one of the required fields is specified indirectly through another table - or, in other words;
SELECT ... FROM BIGTABLE WHERE KFIELD = 123
runs in ms but
SELECT ... FROM BIGTABLE, LTLTBL WHERE KFIELD = LTLTBL.LOOKUP
AND LTLTBL.UNIQUEID = 'STRINGREPOF123'
takes 30 - 40 seconds.
I've managed to work around this first problem by using a function that basically lets me do this;
SELECT ... FROM BIGTABLE WHERE KFIELD = MYFUNC('STRINGREPOF123')
which also runs in ms.
The problem, however, is that this approach only works when there is a single value returned by MYFUNCT but I have some cases where it may return 2 or 3 values.
I know that the SQL
SELECT ... FROM BIGTABLE WHERE KFIELD IN (123,456,789)
also returns in millis so I'd like to have a function that returns a list of possible values rather than just a single one - is this possible?
Sadly the application is running on Sybase ASA 9. Yes I know it is old and is scheduled to be refreshed but there's nothing I can do about it now so I need logic that will work with this version of the DB.
Thanks in advance for any assistance on this matter.
