custom function in statement question

Hi all,

I'm trying to use a custom function in a query I want to use. The custom function I've written takes two numeric arguments, and return a number of 0 or 1, which I want to use to sort data with.

However, I wish to use the distinct keyword in my SQL, which is giving me some problems. The actual SQL i want to execute is more complicated, referencing more tables etc., but for sake of simplicity I can reproduce my problem with a much more simple example below.

An Sql statement like the one below works well:

SELECT product_id FROM product ORDER BY custom_function(product.attr_1, product.attr_2)

where the following statement won't execute:

SELECT DISTINCT product_id FROM product ORDER BY custom_function(product.attr_1, product.attr_2)

instead, sqlplus gives me: "Error at line 1: ORA-01791: not a SELECTed expression".

If I substitute my custom function with a simple field from the product table, it works fine as something like:

SELECT DISTINCT product_id FROM product ORDER BY product.price

I'm sure it's my vast lack of Oracle / SQL knowledge which is causing this error, but if anyone could give me any pointers / references to look at on *why* this doesn't work when using the distinct keyword, it would be greatly appreciated!

Thanks,

Ian. Received on Tue Feb 07 2006 - 11:56:42 CST