Home » Developer & Programmer » Reports & Discoverer » cannot sort varchar2 field as numeric in report builder 6i
cannot sort varchar2 field as numeric in report builder 6i [message #247555] Tue, 26 June 2007 06:33 Go to next message
Abeer30
Messages: 110
Registered: March 2007
Senior Member
Hello,

I am using report builder 6i.
I have the following query:

select segment1,otherfields
from po_headers_all
order by to_number(segment1)

Please note that "segment1" is a varchar2 field.

I'm using "segment1" as a group by field in the report builder wizard.

The above query doesn't give the expected ordering in report builder output while it does from Toad and i don't know why?

Can you help me please?

Regards,

Report message to a moderator

Re: cannot sort varchar2 field as numeric in report builder 6i [message #247672 is a reply to message #247555] Tue, 26 June 2007 13:50 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you post sample data, copy-paste SQL*Plus output (which would be "correct") and a screenshot (JPG, not BMP!) of the report output?

BTW, why do you store numbers in a character column? Are you aware of the fact that this query (and the whole report) will fail as soon as someone enters a character into this column?

Report message to a moderator

Re: cannot sort varchar2 field as numeric in report builder 6i [message #247779 is a reply to message #247672] Wed, 27 June 2007 03:50 Go to previous messageGo to next message
Abeer30
Messages: 110
Registered: March 2007
Senior Member
hello,

my query is as follows:

select distinct ph.SEGMENT1 AS "PO Number"
,ai.INVOICE_DATE
,pl.UNIT_MEAS_LOOKUP_CODE as "Unit of Measure"
,pl.UNIT_PRICE as "Unit Price"
,pl.QUANTITY as Quantity
,(pl.QUANTITY*pl.UNIT_PRICE) as "total"
from
PO_HEADERS_ALL ph
, PO_LINES_ALL pl,
PO_VENDORS pv
, MTL_CATEGORIES_B mc
,po_line_locations_all pla
,ap_invoices_all ai
,ap_invoice_distributions_all aid
,po_distributions_all pd
where
ph.PO_HEADER_ID=pl.PO_HEADER_ID and
pv.VENDOR_ID=ph.VENDOR_ID and
pl.PO_LINE_ID=pla.PO_LINE_ID and
pla.LINE_LOCATION_ID=pd.LINE_LOCATION_ID and
pd.PO_DISTRIBUTION_ID=aid.PO_DISTRIBUTION_ID and
ai.INVOICE_ID=aid.INVOICE_ID and
(pl.QUANTITY*pl.UNIT_PRICE)<>0 and
mc.CATEGORY_ID=pl.CATEGORY_ID
Order by
TO_NUMBER(ph.SEGMENT1)


you can find the report builder output first page screenshot attached.

toad sheet is attached next message.../forum/fa/2662/0//forum/fa/2662/0/
  • Attachment: 6i output.JPG
    (Size: 132.46KB, Downloaded 641 times)

Report message to a moderator

Re: cannot sort varchar2 field as numeric in report builder 6i [message #247781 is a reply to message #247555] Wed, 27 June 2007 03:54 Go to previous messageGo to next message
Abeer30
Messages: 110
Registered: March 2007
Senior Member
hello,

the toad complte output is attached in the following excel file.

thank you...

Report message to a moderator

Re: cannot sort varchar2 field as numeric in report builder 6i [message #247794 is a reply to message #247555] Wed, 27 June 2007 04:25 Go to previous messageGo to next message
Abeer30
Messages: 110
Registered: March 2007
Senior Member
hello,

WELL I HAVE CHANGED THE REPORT STYLE FROM "GROUP LEFT" TO "TABULAR" AND TO MY SURPRISE IT GAVE THE EXPECTED ORDERING AND I HAVE NO IDEA WHY!

ANYWAY, MY PROBLEM IS SOLVED.

THANK YOU.

Report message to a moderator

Re: cannot sort varchar2 field as numeric in report builder 6i [message #248018 is a reply to message #247794] Wed, 27 June 2007 15:42 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is it, perhaps, because of the "Break Order" property?

I'm glad you solved the problem; however, next time, please, do not attach XLS, DOC and similar files - some Forum members can not (while the others do not want to) download and open such a files.

Report message to a moderator

Re: cannot sort varchar2 field as numeric in report builder 6i [message #248721 is a reply to message #248018] Sun, 01 July 2007 00:27 Go to previous message
Abeer30
Messages: 110
Registered: March 2007
Senior Member
Sorry I didn't realize that. Embarassed
I'll make sure that I will not upload anymore excel files.

Report message to a moderator

Previous Topic: Can anyone give me a example of XML publisher within ERP?
Next Topic: Report Execution
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jul 05 05:48:27 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.