How to order a block by a non-block-table field

[Jarlaxle]

Hello, my problem is the next:

I have a multiple record block, with 4 data block fields and 1 non-data-block field, and the point is that I need to sort the block by that non-data-block field, but if I add it to the "ORDER BY clause", at execution it says that " isnt a valid column name. Any idea?

P.D (I must add that I need to be able of insert and update the block records, not only query them).

 

[sem]

Try to create a stored function calculating (returning) the value of your non base table field and order by it.

 

[Jarlaxle]

Can you be more explicit?

 

[sem]

I'm not sure your case is as simple as my example but the idea is:

You have a table my_tab(id number, amount number);
You have a block MY_TAB_BLOCK based on this table with base table items ID and AMOUNT and non base table item DOUBLE_AMOUNT. The value of :MY_TAB_BLOCK.DOUBLE_AMOUNT is calculated as 2*MY_TAB_BLOCK.AMOUNT . You may create
function double(pNum in number) return number is
begin
return 2*pNum;
end;

and order your block by DOUBLE(AMOUNT).

 

[Jarlaxle]

I tried somthing like that, but it continues with the error "not is a valid column name". I think that isn't the same case of your example.

 

[sem]

I've also tried this, it must work. If you're getting "invalid column name" you're probably doing smth wrong. Does your ORDER BY contains the name of STORED FUNCTION with COLUMN (not item) NAMES as its parameters? Press DISPLAY_ERROR key after getting error and look at the query. Is it correct? Does it contain ORDER BY clause? Try to run it from sql*plus and find the place of error.

 

[Jarlaxle]

At last, I added the field to the table, because I need the program running as soon as possible...anyway, I apreciate very much your help, thanks.