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 Go to next message
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 #417311 is a reply to message #417306] Fri, 07 August 2009 02:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
By the looks of it, you don't have an order by clause on your query.
Re: Sorting in Reports with user defined object types [message #417316 is a reply to message #417311] Fri, 07 August 2009 03:04 Go to previous messageGo to next message
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 #417317 is a reply to message #417316] Fri, 07 August 2009 03:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you do not add an explicit ORDER BY, resultsets from select-queries are unsorted.
They may LOOK to be sorted, but that is purely coincidental; outcome is not consistent and not guaranteed, unless you use ORDER BY.
Re: Sorting in Reports with user defined object types [message #417319 is a reply to message #417317] Fri, 07 August 2009 03:27 Go to previous messageGo to next message
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 #417322 is a reply to message #417319] Fri, 07 August 2009 03:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ah, sorry for that.
As I have TOAD nor Reports, I cannot help you out any further.
Re: Sorting in Reports with user defined object types [message #417566 is a reply to message #417319] Mon, 10 August 2009 05:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Whereabouts in reports are you calling DBMS_SESSION?
Re: Sorting in Reports with user defined object types [message #417582 is a reply to message #417566] Mon, 10 August 2009 05:59 Go to previous messageGo to next message
Ariadne
Messages: 7
Registered: September 2004
Junior Member
I am calling at the BeforeReport Trigger
Re: Sorting in Reports with user defined object types [message #417599 is a reply to message #417306] Mon, 10 August 2009 07:30 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Can you try unsetting the break order on all the columns - I suspect that is the cause of the problem but we need to check.
Re: Sorting in Reports with user defined object types [message #417604 is a reply to message #417599] Mon, 10 August 2009 07:53 Go to previous messageGo to next message
Ariadne
Messages: 7
Registered: September 2004
Junior Member
I have tried it, but then I get "Group has no break columns".
Re: Sorting in Reports with user defined object types [message #417608 is a reply to message #417306] Mon, 10 August 2009 08:16 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then get rid of the groups to test it.
Re: Sorting in Reports with user defined object types [message #417614 is a reply to message #417604] Mon, 10 August 2009 08:57 Go to previous message
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.
Previous Topic: Reports 10G Spreadsheet output
Next Topic: XML output column
Goto Forum:
  


Current Time: Sun Dec 11 06:10:31 CST 2016

Total time taken to generate the page: 0.09556 seconds