sort does not work with mailaddress values in a column

Narendar_Reddyk

Hello,

we have a webreport where we are trying to sort on column with mailaddress. but it does not work.

any help appreciated.

Thnx

N

Lindsay_Davies

Hi Narendar,

 

Sort does work, but it depends on what column you wish to sort as to how you go about doing it.

 

I assume you are trying to do an order by clause on a sql select statement to sort your rows.

 

Some mail address columns are of longer data types and in their raw state they cannot be sorted in an order by clause.

For example, anywhere we store the “To” field for an email  needs to be able to store a long string as it can have many addresses stored in it – almost an unlimited number.

For a user’s own email address, the length is only 255 which can be sorted by the DB without special handling.

 

The data type varies from Oracle to SQL Server, as does the trick for dynamically changing it so it can be used in an order by clause.

 

A straightforward single email address field like KUAF.MailAddress will be varchar(255) in Oracle and nvarchar(255) in SQL Server.

 

However, in OTEmailProperties, columns such as OTEmailTo and OTEmailCC could contain hundreds of addresses, so have a data type of clob in Oracle and ntext in SQL Server.

 

 

Using the OTEmailTo column as an example…

 

In Oracle

            select OTEmailTo from OTEmailProperties order by dbms_lob.substr(OTEmailTo, 0, 100)

 

If this is SQL Server, you can use

select OTEmailTo from OTEmailProperties order by cast(OTEmailTo as nvarchar(100))

 

You may want to restrict the length of the selected column too and/or order by more than the first 100 characters.

 

Regards

Lindsay

 

Lindsay Davies

Principal Product Specialist

European Escalation Team

Opentext UK

 

 

 

 

 

 

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2013 April 30, Tue 14:25
To: eLink Recipient
Subject: sort does not work with mailaddress values in a column

 

sort does not work with mailaddress values in a column

Posted bynarendar.reddyk@philips.com (Reddyk, Narendar) On 04-30-2013 09:21

 

Hello, we have a webreport where we are trying to sort on column with mailaddress. but it does not work. any help appreciated. Thnx N

---

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center