Issue with substring function in cognos report studio 10.2

[vvasireddy9]

I am having hard time with the logic below:
-------------------------------------
case when (position('%',?p_Accountcd?) = 1)
then (case when (position('%',substring(?p_Accountcd?,2)) >1)
            then ('%'||CAST(substring(?p_Accountcd?,2,(if(position('%',substring(?p_Accountcd?,2))>0) then (position('%',substring(?p_Accountcd?,2))) else (0))
-1),VARCHAR(100))||'%')
            else (('%'||CAST(substring(?p_Accountcd?,2) AS VARCHAR(100))))
           end
)
when (position('%', ?p_Accountcd?)  > 1)
THEN (substring(?p_Accountcd?,1,position('%', ?p_Accountcd?) -1)||'%')
else (?p_Accountcd?)
end
----------------------------------------

The logic works fine when it is the only data item in a cognos query. The error shows up when I add any more data items to the query or when using this logic in a filter. Below is the error:
-------------------------------------
UDA-SQL-0115 Inappropriate SQL request.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Invalid length parameter passed to the substring function.
------------------------------------
Anyone faced similar issues? DB is SQL Server and cognos 10.2

[teresa.danna@gmail.com]

Its possible this comes down to your cast function:

CAST(substring(?p_Accountcd?,2) AS VARCHAR(100))

Try instead:

CAST(substring(?p_Accountcd?,2), varchar(100))

Let me know if that worked.

[naqsa]

I'm trying to truncate the # of characters using the substring function. When I use the function in a report with a single column, it works fine. But when I use the same in a report with multiple columns, I receive no data.

substring ([Expense].[Expense Allocation].[Account Code 1],1,5)

Can someone please help me figure out what I'm doing wrong? This is for Concur. 

[MFGF]

I'm trying to truncate the # of characters using the substring function. When I use the function in a report with a single column, it works fine. But when I use the same in a report with multiple columns, I receive no data.

substring ([Expense].[Expense Allocation].[Account Code 1],1,5)

Can someone please help me figure out what I'm doing wrong? This is for Concur.

Hi,

Where are you using this expression? In a query calculation? In a filter expression? Somewhere else? Can you provide mode details and the complete expression you are using?

MF.

[naqsa]

I'm entering this in the 'expression definition' box. Thanks for your help!

[Lynn]

I'm entering this in the 'expression definition' box. Thanks for your help!

Expression definition box of....

Hi,

.... a query calculation? In a filter expression? Somewhere else? Can you provide mode details and the complete expression you are using?

MF.

[MFGF]

I'm entering this in the 'expression definition' box. Thanks for your help!

Really? Wow.

Imagine I had a problem with my car. I posted that when I press a button the lights go off. You replied, trying to help,  saying "which button are you pressing?". I then replied saying "A button in the car"

I'm sure you'd be quite bemused by my complete and utter lack of detail, and my apparent unwillingness to share even the tiniest tidbit of useful information.

I hope you can see that your reply above is the exact same paradigm.

MF.

[naqsa]

Hi MFGF,
Point noted.

For anyone that may find this useful:
For anyone using Concur and trying to truncate field to a specific # of characters:
On the column you wish to truncate, double click (not the header but the boxes below the header) to open the expression definition box. Navigate to Functions  --> Common Functions --> substring --> double clicked, added parentheses and field length, ended up with -->  substring ([Expense].[Expense Allocation].[Account Code 1],1,20) .
Checked for errors and saved. Re-ran report and the field was now truncated to 20 characters.