Possible?

Is it possible to use a variable as the column name in an order by clause in a stored function? This example doesn't work, but should give an idea of my goal... ============================================ CREATE FUNCTION sort_test (SORTBY varchar(32)) RETURNING char(10), varchar(64) DEFINE VAR1 char(10); DEFINE VAR2 varchar(64); FOREACH SELECT col1, col2 INTO VAR1, VAR2 FROM my_table ORDER by SORTBY RETURN VAR1, VAR2; END FOREACH END FUNCTION; ============================================ THEN EXECUTE FUNCTION sort_test ("col1"); I know I can do some logic to test for the value of SORTBY, then have different FOREACH SELECT... statements for the different possible SORTBY values, I was just hoping for a little smoother method. THANKS!! bobd

No, Bob, what you want amounts to dyanmic SQL which is not permitted in SPL. Now when IDS.2000 gets here and you can write Java procedures THEN you will be able to do what you want! Art S. Kagel Bob Damato wrote: > > Is it possible to use a variable as the column name in an order by > clause in a stored function? > > This example doesn't work, but should give an idea of my goal... > > ============================================ > CREATE FUNCTION sort_test (SORTBY varchar(32)) > RETURNING char(10), > varchar(64)> > DEFINE VAR1 char(10); > DEFINE VAR2 varchar(64); > > FOREACH SELECT col1, > col2 > INTO VAR1, > VAR2 > FROM my_table > ORDER by SORTBY > > RETURN VAR1, VAR2; > END FOREACH > > END FUNCTION; > ============================================ > > THEN > > EXECUTE FUNCTION sort_test ("col1");> > I know I can do some logic to test for the value of SORTBY, then have > different FOREACH SELECT... statements for the different possible SORTBY > values, I was just hoping for a little smoother method. > > THANKS!! > bobd