Home » Developer & Programmer » Reports & Discoverer » Sorting in Reports with user defined object types
| Sorting in Reports with user defined object types [message #417306] |
Fri, 07 August 2009 02:20  |
Ariadne
Messages: 7
Registered: September 2004
|
Junior Member |
|
|
Hello!
I have a problem with sorting involving Reports and user defined objet types.
I have created the following object types
CREATE TYPE type_balance_compte AS OBJECT
(
NUM_CPT_SEQ NUMBER
,NUM_CPT VARCHAR2(35)
...
)
CREATE TYPE TB_type_balance_compte IS TABLE OF type_balance_compte
At the reports query I use:
SELECT ...
FROM table(cast(test_pkg.balance_comptes(:P_num_soc) as TB_type_balance_compte)) c
The procedure balance_comptes will retrieve data from various tables into the type.
The report is ordered by a certain string field that usually contains characters and numbers.
I need to have numbers always before characters, meaning the data should come in this order in the report for example:
0
1
A
B
So, before the report query, I have placed a call to DBMS_SESSION.SET_NLS( 'nls_sort', 'binary' ) to guarantee NLS_SORT in case it is originally set to FRENCH.
The problem here is that even after this call, I have the report ordered like this
A
B
0
1
And not the numbers before as it should be.
To try and find out where the problem was, I have created a table to use instead of the object type described above. In this case, it worked correctly. So all I know by now is that is has something to do with the type or cast, but what exactly? Does anybody now how to solve this without using a table?
Many thanks
Ariadne
|
|
|
|
|
|
| Re: Sorting in Reports with user defined object types [message #417316 is a reply to message #417311] |
Fri, 07 August 2009 03:04  |
Ariadne
Messages: 7
Registered: September 2004
|
Junior Member |
|
|
|
Unfortunately, I do have an order by. If I execute the query at TOAD and change the NLS_SORT it gives me different results if it is BINARY or FRENCH. However, if I call the reports with the same query and change the NLS_SORT to BINARY at before_report trigger, it gives me always the FRENCH order (i.e. characters before number).
|
|
|
|
|
|
| Re: Sorting in Reports with user defined object types [message #417319 is a reply to message #417317] |
Fri, 07 August 2009 03:27 |
Ariadne
Messages: 7
Registered: September 2004
|
Junior Member |
|
|
This is the query I have at the report:
SELECT c.num_soc num_soc
, decode(:P_par_018
,NULL,NULL
,decode(c.cod_cat_cpt ,'X',substr(c.num_cpt,1,1)
,'S',substr(c.num_cpt,1,1),'0')) Saut_compta
, decode(c.cod_cat_cpt,'X',substr(c.num_cpt,1,1)
,'S',substr(c.num_cpt,1,1),'0') compta
, decode(:P_par_018,NULL,NULL,
decode(c.cod_gen_pln
,'L',c.cod_cat_cpt
,'U',c.cod_cat_cpt
,substr(c.cod_cla_cpt,1,1))) Saut_de_page
, decode(:P_par_018,NULL,NULL,c.tri_cla) Saut_de_page_cla
, c.cod_cat_cpt cod_cat_cpt
, c. service
, c.val_zon_tri val_zon_tri
, c.tit_cat
, c.tri_cla tri_cla
, c.classe classe
, c.cod_tit cod_tit
, c.num_cpt compte
, decode(:p_par_010,'X',c.lib_1er,c.lib) lib
, c.lib_2em lib_2em
, c.lib_3em lib_3em
, c.min_rub_sai min_rub_sai
, NLSSORT(c.rub_sai) rub_sai
, c.col1
, c.col2
, c.col3
, c.col4
, c.dat_com_vld
, c.dat_fin_vld
, c.lib_classe
FROM table(cast(pa_cgl324.balance_comptes( .... ) as TB_type_balance_compte)) c
WHERE num_soc = :P_num_soc
&p_and
ORDER BY num_soc,saut_compta, saut_de_page, cod_cat_cpt, saut_de_page_cla, tri_cla, classe, val_zon_tri, compte, rub_sai;
The break order property is set for the same columns as those in the order by clause.
At regedit, NLS_SORT=FRENCH.
If I go to TOAD and execute DBMS_SESSION.SET_NLS( 'nls_sort', 'binary' ) and then the query, I get data in a certain order.
If I do the same thing at reports, I get data in a different order (i.e. A-Z before 0-9)
[EDITED by LF: applied [code] tags]
[Updated on: Fri, 07 August 2009 04:36] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Sorting in Reports with user defined object types [message #417614 is a reply to message #417604] |
Mon, 10 August 2009 08:57 |
Ariadne
Messages: 7
Registered: September 2004
|
Junior Member |
|
|
By removing the groups I am obliged to remove repeating frames as well and I get a completely different report. Then I am not even able to know if the order is correct or not.
Weird thing is, we have a similar report that does not use a user defined types and it doesn't have the same problem.
|
|
|
|
Goto Forum:
Current Time: Mon May 20 15:27:31 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
