Dynamic Order By
Steve, March 15, 2002 - 3:47 am UTC
Tom,
Is their anyway then to do a dynamic order by without incurring another parse??? The reason I ask is we tend to use Lexical Paramters in or report builder reports to allow the user to specify the order by clause, but after reading your great book I've realised the error of our ways and I'm now trying to find an alternative method.
March 15, 2002 - 1:36 pm UTC
No (well yes and no), but -- it should be OK in this case.
For example, say you have 5 columns. That could result in LOTS of different where clauses (tons of them, every combination of the columns taken 0, 1, 2, 3, 4 or 5 at a time -- and EACH with unique values to be compared with). The where clause would generate an INFINITE number of queries.
It will probably result in only 5 different "order by"s though. (or some smallish number, even in the low 100s maybe). It is finite.
So -- you want to bind in the where clause, in the ORDER BY clause -- you want the optimizer to see each order by as UNIQUE.
Eg:
select * from emp where job = :bv order by ename;
select * from emp where job = :bv order by hiredate;
suppose there was an index on (job,hiredate). You would WANT two different plans for each of the above queries!! One should use the index to access AND get the data sorted. The other should use the index to access the data and then invoke a sort.
Don't worry about the binds in the order by.
I like this decode
andrew, March 15, 2002 - 2:53 pm UTC
Lexical Variable Usage in Reports
Michelle Monteiro, October 23, 2002 - 7:51 am UTC
Hi Tom
We use lexical variables in our Reports to improve flexibility, it enables us to use one report as opposed to producing several, simply by changing the values of the lexicals (column and table definitions).
e.g table a has empno, ename
table b has empno, job
so we would
select empno, &P_col P_FIELD
from &P_TABLE
This all works fine, however if I want to know the value of :P_FIELD and act accordingly I run into problems e.g
IF :P_FIELD = 'Smith' THEN...
The value still reads the passed value of 'ename'.
Is there a value of reading the data value of the returned lexical column variable and not the passed in string???
October 23, 2002 - 8:34 am UTC
try the discussion forums on otn.oracle.com under "developer".
I've never actually written a report in my entire life.
I can't recreate this problem!
Steven, October 23, 2002 - 9:19 am UTC
Michelle,
I use lexical parameters in reports, but I have never experienced this problem....I have just create a test report in Report Builder ver 6, which works as expected.
heres
what I have done:
1) Create lexical parameter called P_1 initial value = sal.
2)Create sql query :
SELECT EMPNO , &P_1 C_LEX
FROM EMP
3) use layout wizard to generate default layout.
4) attach PLSQL format trigger to the C_Lex field :
function F_SALFormatTrigger return boolean is
begin
IF :C_LEX <= 3000 THEN
return (TRUE);
ELSE
return (FALSE);
END IF;
end;
This works as it should i.e. anybody with a sal > 3000 and their sal won't be shown.
Hope this helps
Sorting issues...
Alvin, July 29, 2003 - 3:59 am UTC
sys@RTBE.CRONOS> create table t (name char(1));
Table created.
sys@RTBE.CRONOS> insert into t values ('B'
2
sys@RTBE.CRONOS>
sys@RTBE.CRONOS> insert into t values ('B')
2 /
1 row created.
sys@RTBE.CRONOS> insert into t values ('c')
2 /
1 row created.
sys@RTBE.CRONOS> insert into t values ('e');
1 row created.
sys@RTBE.CRONOS> insert into t values ('F');
1 row created.
sys@RTBE.CRONOS> insert into t values ('a');
1 row created.
sys@RTBE.CRONOS> insert into t values ('D');
1 row created.
sys@RTBE.CRONOS> commit;
Commit complete.
sys@RTBE.CRONOS> select name from t order by name;
NAME
--------------------------------------------------
B
D
F
a
c
e
6 rows selected.
sys@RTBE.CRONOS> show parameter sort
NAME TYPE VALUE
------------------------------------ ------- -----------
nls_sort string latin
sort_area_retained_size integer 500000
sort_area_size integer 500000
sort_multiblock_read_count integer 2
I changed the nls_sort setting in the init.ora and bounced the db but i'm not getting the result as below.
sys@RTBE.CRONOS> alter session set nls_sort=latin;
Session altered.
sys@RTBE.CRONOS> select name from t order by name;
NAME
--------------------------------------------------
a
B
c
D
e
F
6 rows selected.
Do i need to specifically change my session setting (via alter session)to get the 2nd result set ?
July 29, 2003 - 7:14 am UTC
select * from nls_session_parameters
and
select * from nls_database_parameters
if the client sets ANY of the NLS_* parameters, then all others default -- overriding the datbase default.
So, my guess is that your client is setting NLS_LANG and that is causing ALL other NLS parameters to default and the default sort is binary.