Sort doesn't work-please help

[mutinda1967]

Hi guys,
I defined the parameter &sort then tried the following in my report but it didn't work

select, lastname, City
From prospect
ORDER BY &SORT; -- where &sort would be Lastname,city

when I run this I get error ORA-00936

what am I missing here?

 

[BJCooperIT]

ORA-00936 is a missing expression. It sounds as though your &SORT is not valued when the SQL is executed.

select * from Life where Brain is not null

Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
[sup]When posting code, please use TGML to help readability. Thanks![sup]

 

[mkpatham]

Change &sort to :sort in the query and run it.

 

[sem]

Don't change &sort to :sort, because substituting bind variables is performed after parsing stage, so parser will obviously generate syntax error. I suppose you problem is in missing VALID DEFAULT VALUE for SORT variable. If in some cases you need the results to be unsorted, you may use the whole ORDER BY clause as lexical variable:

select, lastname, City
From prospect &order_by_clause


where order_by_clause may be
'ORDER BY Lastname,city'



Regards, Dima