How to use SQL Order By statement to sort results case insensitive?

Azarudeen2991

I am trying to sort the attribute value of custom object type using the below DQL:

select distinct object_name from ds_diary_docset order by 1

I get the results like :

ACG, ACT1, ADDPLAN AF, AEGIS, Active Per1, ActivePython 2.5.2

What i want to get:

ACG, ACT1, Active Per1, ActivePython 2.5.2, ADDPLAN AF, AEGIS

Kindly proive your valuable suggestion to get the above results?

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

All comments

DCTM_Guru

Are you querying against registered table, since Content Server requires case sensitivity? If so, we had similar situation where external lookup tables were case insensitive. To get around this, we asked the dba to create case-sensitive view against original case insensitive lookup table.

Alvaro_de_Andres

select distinct(object_name),upper(object_name) from ds_diary_docset order by 2;

Azarudeen2991

@Alvaro_de_Andres said:
select distinct(object_name),upper(object_name) from ds_diary_docset order by 2;

If I want to display the results of the above query in property page configuration element, the upper case results are only coming instead the actual object name.

is it possible to display the case insensitive results in property page of D2?

DCTM_Guru

Have you tried using aliases:

select distinct(object_name) as object_name,upper(object_name) as ordered_name from ds_diary_docset order by 2;

Azarudeen2991

@DCTM_Guru said:
Have you tried using aliases:

select distinct(object_name) as object_name,upper(object_name) as ordered_name from ds_diary_docset order by 2;

yes tried using aliases. still not working.

DCTM_Guru

If you cant get the DQL order to work, the additional view should work as well.

Azarudeen2991

@DCTM_Guru said:
If you cant get the DQL order to work, the additional view should work as well.

If the view is created, can it be mapped to the field of the property page in D2?.

DCTM_Guru

How is mapped now? If you have a property mapped to a lookup table, changing it to a view is no different.