Sorting Issue

[jyothi_ph]

I am facing an issue in my report i have two columns in my report(subject,count) and the subject column is sorted in ascending order it works fine but in some recods(Q.use.order) is in small case which i dont want to sort and want to display that value(Q.use.order) at last.i am attaching the screenshot.

Subject                            Count
Q.TESTORDER                    100
Q.use.order                         67
Q.VEST1.ORDER1                  90
Q.WEST.ORDER                      7

I want the output Like this

Subject                            Count
Q.TESTORDER                    100
Q.VEST1.ORDER1                  90
Q.WEST.ORDER                      7
Q.use.order                         67

[blom0344]

On SQL server with SQL function set enabled:

create dataitem ascii(substring(subject,3,1))

which has range:

65-90 for  A - Z
97-122  for a - z

Ordering by this object gives Upper Cases first and then lower cases

[jyothi_ph]

i am getting the following error

The operation "ascii" is invalid for the datatype "varchar".

[blom0344]

try casting the substring(subject,3,1) part to char(1) before applying the ascii function

[jyothi_ph]

Hi still i am not able to resolve the issue the same error is comming can u plz give some more details/steps.

i am using this query ascii(cast(substring(subject,3,1) as char(1)))

[blom0344]

Just tested it on a SQL 2005 database. No problem..
The package will need to have the specific function set enabled (in this case SQL server) for the ascii function to be reckognized

[jyothi_ph]

Hi i am able to run from SQl Server but when the ascii function is used in cognos the following error comes

RQP-DEF-0177

An error occurred while performing operation 'sqlPrepareWithOptions' status='-126', UDA-SQL-0460.

[blom0344]

When publishing your package you need to add the SQL server function set, otherwise you cannot use SQL functions from within Cognos..

[jyothi_ph]

But i am able to see the ascii function in the Vendor specific functions.

When checking the expression the following error is comming

UDA-QOS-0006Error
UDA-SQL-0460 A general exception has occured during local processing.
UDA-EE-0093 The operation "ascii" is invalid for the data type "character'.

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.

[blom0344]

The  error refers to 'local processing'. There must be something with the query/package that disables the execution on the database.

What does the SQL look like with the additional dataitem?