order by clause

[admin]

Hello everyone, we are on Datastage version 4.1 on an NT Server, using the ODBC stage as a lookup to a Sql Server 7.0 table.

Its possible to get more than one row on a lookup, and I want to return the first row based on the order by clause. I cant seem to get the order by clause working, even when I specify Reference link with multi-row result set in the link properties. Any help would be greatly appreciated.


WHERE Clause:

infoman.F3 = 001 AND infoman.F4 = 2 AND ((infoman.F2 BETWEEN 601 AND 615) OR (infoman.F2 BETWEEN 617 AND 631) OR infoman.F2 = 633)

Other Clauses:

ORDER BY infoman.F1 DESC, infoman.F2 DESC, infoman.F5 ASC, infoman.F3 DESC


View SQL:

SELECT infoman.F1, infoman.F2, infoman.F3, infoman.F4, infoman.F5, infoman.F6 FROM "infoman"
WHERE F3 = 001 AND F4 = 2 AND ((F2 BETWEEN 601 AND 615) OR (F2 BETWEEN 617 AND 631) OR F2 = 633) AND (infoman.F1 = ?);