Its very useful
AnithaGirish, August 19, 2001 - 7:07 am UTC
Ive tested this its very useful and simple
how about ASC/DESC
Clayton, September 14, 2002 - 10:02 pm UTC
Very helpful! Could the ASC/DESC qualifier be set dynamically like the ORDER BY column was?
September 15, 2002 - 10:54 am UTC
well, it is much harder if you use this decode trick.
You would have to come up with a way to collate the string in either ASC or DESC order. Sure, you could do it but it gets quite hard.
I would be using dynamic sql at that point personally.
Dynamic ref cursor when using collections in the cursor
Dushan, November 03, 2004 - 12:20 pm UTC
Hi Tom,
We have currently problem with API function - there is a request to pass new parameter p_order_by, which defines order by clause. It should work like this:
p_order_by=1, ORDER BY ACC_NUM_REM, var_symb
p_order_by=2, ORDER BY ACC_NUM_REM desc, var_symb
etc, many variations
The best would be to use dynamic ref cursor, but in our API procedure, we use collection p_bank_num_type as an input parameter - I have't found way how to rewrite current ref cursor to dynamic form.
CREATE OR REPLACE FUNCTION GETALLPAYMENTS
(p_bank_num_type bank_num_type
,P_STATUS1 IN sczp.trans.status1%TYPE := null
,P_STATUS2 IN sczp.trans.status2%TYPE := null
,P_STATUS3 IN sczp.trans.status3%TYPE := null
,P_TRAN_ID_BULK IN sczp.trans.tran_id_bulk%TYPE := null
,P_ACC_STMNT_DATE_FROM IN sczp.trans.acc_stmnt_date%TYPE := null
,P_ACC_STMNT_DATE_TO IN sczp.trans.acc_stmnt_date%TYPE := null
,P_VAR_SYMB IN sczp.trans.var_symb%TYPE := null
,P_PSTAV IN sczp.trans.pstav%TYPE := null
,P_TRN_TYPE IN sczp.trans.trn_type%TYPE := null
,p_max_row number :=1000000
,p_min_row number :=1
,p_order_by number:=null
)
RETURN KORP_PKG.T_REF
IS
l_t_ref korp_pkg.t_ref;
begin
OPEN l_t_ref FOR
select *
from ( select /*+ first_rows */
a.*
,rownum rnum
from ( select /*+ index(a,trn_idx_17) */
a.id_tran
,a.tran_id_bulk
,a.ACC_PRE_NUM_REM
,a.ACC_NUM_REM
,a.var_symb
,count(b.id_tran) cnt_id_tran_bulk
from sczp.trans a
,sczp.trans b
,table (cast(p_bank_num_type as bank_num_type)) c
where 1=1
and a.id_tran = b.tran_id_bulk(+)
and b.pstav(+) ='N'
and a.trn_type = nvl(p_trn_type, a.trn_type)
and a.STATUS1 = nvl(p_status1,a.STATUS1)
and a.STATUS2 = nvl(p_status2,a.STATUS2)
and a.STATUS3 = nvl(p_status3,a.STATUS3)
and a.ACC_NUM_REM = c.ACC_NUM_REM
and a.ACC_PRE_NUM_REM = c.ACC_PRE_NUM_REM
and a.BANK_NUM_REM = c.BANK_NUM_REM
and a.var_symb||'' = nvl(p_var_symb,a.var_symb)
and a.pstav||'' = nvl(p_pstav,a.pstav)
group by a.id_tran
,a.tran_id_bulk
,a.ACC_PRE_NUM_REM
,a.ACC_NUM_REM
,a.var_symb
) a
where rownum <= p_max_row
)
where rnum >=p_min_row;
return l_t_ref;
end;
/
Any idea how to construct ORDER BY clause in this case?
Thanks,
Dushan
November 04, 2004 - 9:29 am UTC
whats this:
and a.var_symb||'' = nvl(p_var_symb,a.var_symb)
and a.pstav||'' = nvl(p_pstav,a.pstav)
yuck. anyway -- so why cannot you make this native dyanamic sql?
but you can always
<quote>
p_order_by=1, ORDER BY ACC_NUM_REM, var_symb
p_order_by=2, ORDER BY ACC_NUM_REM desc, var_symb
etc, many variations
</quote>
order by decode( p_order_by, 1, acc_num_rem ),
decode( p_order_by, 2, acc_num_rem ) DESC,
.......
interesting Translating
Kerry, November 04, 2004 - 4:05 pm UTC
I saw that your dynamic ordering for numbers was really complex looking, but obviously works nicely.
It would be easy to write it this way for easier understanding:
select *
from t
order by decode( upper(:order_col),
'X', x+100000000000000000,
'Y', y,
'Z', to_char(z,'yyyymmddhh24miss'));
November 05, 2004 - 3:10 pm UTC
negative numbers.....
what is Y? a string I assume
what is the 1000000000000000 for?
RE:Dynamic ref cursor when using collections in the cursor
Dushan, November 05, 2004 - 7:11 am UTC
Great, this works:
OPEN l_t_ref FOR '
select *
from ( select /*+ first_rows */
a.*
,rownum rnum
from ( select /*+ index(a,trn_idx_17) */
a.id_tran
,a.tran_id_bulk
,a.ACC_PRE_NUM_REM
,a.ACC_NUM_REM
,a.var_symb
,count(b.id_tran) cnt_id_tran_bulk
from sczp.trans a
,sczp.trans b
,table (cast(:x as bank_num_type)) c
......
' using p_bank_num_type, ...
Nevertheless, I really do not need to go for dynamic sql, because the
"order by decode( p_order_by, 1, acc_num_rem ),
decode( p_order_by, 2, acc_num_rem ) DESC,
...."
is what I need.
Thanks a lot!
Dushan
dynamic order by
Mary W, December 15, 2004 - 3:08 pm UTC
Tom,
Is it possible to use dbms_session.set_context for dynamic sort in the order by clause?
For example:
if in_order = 'date' then
l_query:=l_query || 'order by date_field sys_context(''MY_CTX'',''sort'');
end if;
dbms_session.set_context( 'MY_CTX', 'sort', in_sort );
Doing this gives me ORA-0933 SQL command not properly ended error.
I am trying to eliminate all the extra parsing for performance reasons.
thank you
December 15, 2004 - 6:20 pm UTC
No, that requires a new plan entirely.
think about it
order by X asc
vs
order by X desc
you need two entirely different plans for that -- hence, no binding.
the goal of binding is plan reuse.
dynamic select
Nilanjan Ray, December 16, 2004 - 1:43 am UTC
Hi Tom,
Going throgh this thread of dynamic SQL and ref cursor I thought the readers of this thread would be interested on this one. I would also require your valuable opinion on this one. If you feel this is a new question you may ignore it. In that case I might need to wait for ages to post this one. Thanks in advance for your valuable time.
Our application has a query tool implemented through views (there are currently about 20 such views). Users use these views to query data using predicates based on any column(s) exposed by the views. It has been quite a challenge for me to maintain the performance of these views till date.
Some of the views are rather complicated. Some views uses upto 20 tables in joins (including quite a number of outer joins). Certains tables have millions of rows and some use complicated function calls from within the views. I have written the views so that the function calls are applied at the last stage of the query. Over and above the final SELECT statementon the views have a 'order by' clause and the result set is limited by the use of 'rownum' (implemented through a user defined application parameter).
A typical query from the view would look like this:
select col1,col2,col3
from vw_test
where col4 = ..
and ....
order by .....
where rownum <= 4000
The final SQL as above is returned above from a stored procedure (column list, view name and where clause are dynamically constructed) using a ref cursor to a front end java application.
The select list would often contain columns that does not require joins to certain tables (or inline views within the main view) in the views. I was looking for an approach by which I would be able to avoid such joins inside the views when not required, basically to improve performance.
I am confident that the view SQLs are correctly written.
Heres a cut out simplified sample of one of the more complex views.
-------------------------------------------------------------------
CREATE OR REPLACE VIEW vw_test AS
SELECT /*+ no_merge(v1) push_pred(v1) */
v1.col_key
,TO_NUMBER(v1.cm_id) CM_ID
....
,ROUND(My_Pkg.Sf_Get_Rtg (...),2) tvr_actual --Function call
... -- 3 more function calls
FROM
(
SELECT /*+ DYNAMIC_SAMPLING(3) */
spot.spot_key
,TO_NUMBER(scm.cm_id) CM_ID
....
FROM Table_1 Spot
,Table_2 SP
,Table_3 SCM
... -8 more tables
WHERE
.... join conditions. Quite a few outer joins
) v1
,Table_4 scb
,Table_5 scbp
... 16 more table joins.Quite a few outer joins
WHERE
---24 more joins. Quite a few outer joins
---------------------------------------------------------------------
I would like to have your opinion as to whether there are other optimal ways of implementing such requirements.
December 16, 2004 - 8:13 am UTC
<quote>
A typical query from the view would look like this:
select col1,col2,col3
from vw_test
where col4 = ..
and ....
order by .....
where rownum <= 4000
</quote>
you do understand that will
a) get 4,000 random rows that satisfy your predicate
b) sort them
c) return them
you do understand that that is NOT returning the first 4,000 rows after sorting.....
how bad would it be to do it this way?
Mary W, December 16, 2004 - 11:26 am UTC
if in_order = 'date' then
l_query:=l_query || 'order by date_field '||in_sort;
end if;
i checked the v$sql view and saw that this query is parsing 32 times!
December 16, 2004 - 11:47 am UTC
so, what is "in_sort"?
good clarification on sort & count_stopkey
Nilanjan Ray, December 17, 2004 - 6:12 am UTC
Thanks Tom,
Yes you are correct. I am definitely going to take up the issue of misconception you highlighted. What if I do something like this
select *
from
(
select col1,col2,col3,col4
from vw_test
where col4 = ..
and ....
order by col4
)
where rownum <= 4000
does this return the first 4000 rows after sorting OR it rerurns a random 4000 rows from the sorted result set.
Would the next SQL return the first 4000 rows from the sorted list. (However this degrades performance, does not pushes predicate in the view, may be because it would alter result set)
select *
from
(
select rownum rn, col1,col2,col3,col4
from vw_test
where col4 = ..
and ....
order by col4
)
where rn <= 4000
If NOT please suggest best ways to return the first "N" rows from a sorted result set without adverse performance implications.
Refering to my initial question: Please suggest me (if at all possible) of an alternate approach of implementing the sort of requirement I mentioned in my question.
Thanks again
December 17, 2004 - 2:33 pm UTC
select *
from ( .... order by )
where rownum <= 4000
sorts and then returns the first 4000, we call that a "top-n" query.
in the second, rownum is assigned BEFORE the sort -- so, you would get 4000 random rows as your output.
your followup to this review is in itself a chapter in a book type of question. sort of very very broad. too broad for right here.
order incoming parameters
Kan, April 21, 2005 - 3:59 pm UTC
Tom,
Is it possible to order incoming parameters depending on the value (it'll be 'Y' or 'N') and keep all 'Y's first and all 'N's last.
Eg:
Procedure testrows(p_a in char,
p_b in char,
p_c in char,
p_d in char,
p_e in char)
as
-- logic is like this:
-- all the parameters have either 'Y' or 'N'
/* check all the parameters , and put all those have 'Y' in first (in my query WHERE clause)
let's say these are the values
p_a:='Y'
p_b:='N'
p_c:='N'
p_d:='Y'
p_e:='N'
My sql is going to be
select * from test_tab
where (a=p_a and d=p_d)
or(b=p_b and c=p_c and e=p_e);
*/
end;
Hope i was clear in explaining what i need. If not please let me know,
Thanks
April 22, 2005 - 10:04 am UTC
build two strings and glue them together
if p_a = 'Y'
then
l_y_string := l_y_string || ' a=p_a ';
else
l_n_string := ......
......
Kan, April 23, 2005 - 12:38 pm UTC
Tom, Thanks for your reply.
What if we have more than few parameters or have dynamic parameters? we have to change our code for every extra parameter .
I was looking for some kind of looping depending on parameter count and assigning them to different strings. Please give me a generic solution if possible.
Thanks,
April 23, 2005 - 1:03 pm UTC
pass an array, otherwise, you have individual variables and individual variables must be addressed by name.
So, pass an array, arrays can be indexed using a subscript.
Confused!!!
Kan, April 23, 2005 - 11:01 pm UTC
Tom,
Thanks for the reply. When you said pass an array, do you mean get all the parameter values from that procedure and make all these as an a array input to another function which seperates them into Y's and N's? If that's the case, is there any way i can count no of parameters my procedure has , so that i can add them to array?
If not, then how can i pass all these parameters as array?
April 24, 2005 - 5:24 am UTC
You know how many parameters a procedure/function has - you wrote it.
The caller of your procedure would have to pass you an array of inputs, IF you want to iterate over them and reference the i'th element.
Else, all you have is "input1, input2, input3, .... inputN" and you would have to reference each by name on a separate line of code.
Order by with referrence to some other table.
A reader, April 25, 2005 - 9:07 am UTC
Tom,
I've a table with following data.
ref id sequence
-------------------------
00001 1 1
00001 1 2
00001 3 1
00001 4 1
and I'm maintaining the "sort order definition" in another lookup table which looks like this:
id seq
------------
3 1
2 2
4 3
1 4
So I want to sort the main table ID wise where the sort order is to be taken from this lookup table. My output should look like this:
ref id sequnce
------------------------
00001 3 1
00001 4 1
00001 1 1
00001 1 2
I hope I'm clear with this.
Thanks as always!
April 25, 2005 - 9:18 am UTC
join them.
join T1 to T2 by ID and order by seq.
thanks as always
A reader, April 25, 2005 - 9:54 am UTC