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
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
i am getting the following error
The operation "ascii" is invalid for the datatype "varchar".
try casting the substring(subject,3,1) part to char(1) before applying the ascii function
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)))
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
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.
When publishing your package you need to add the SQL server function set, otherwise you cannot use SQL functions from within Cognos..
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'.
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?