How to use curly brackets } in SQL in report studio?

[jimmy1234]

Hi,

I am writing a SQL in report studio that has a curly bracket } in a string that is used in a filter.

e.g.   where a.column1 = 'newyork}london'


The SQL is not getting validated and is giving an error. } is a special character in Cognos and I tried putting in a escape character but it still doesn't work. How can I resolve this issue?

Thanks.

[blom0344]

Just tried it out. In my case Cognos rewrites this to   :

Code Select Expand


where a.column1 = N'newyork}london'



and it validates..

[jimmy1234]

Hi blom0344,

did you write the entire SQL in edited Native SQL? or just a filter in the Query? This issue occurs when writing the entire SQL in edited SQL.

[blom0344]

I used it in a filter, but when I use the } within a Cognos sql query subject it validates without any problem.  Strange..
(In both framework and RS)

[bdbits]

Not sure if Cognos will mangle this before it gets to SQL Server, but to escape characters for TSQL you can surround it with []. So for example.

where somecolumn = '[{]Surrounded![}]'

I did not test this in Cognos. I am a bit short on time today, sorry, but thought I'd throw this out there. So I hope it works. :-)

[cognostechie]

In Cognos, curly brackets are placeholders for Native SQL. I had this problem when the data had curly brackets so I used the 'replace' function to replace the curly bracket with another character and it worked fine.

[jimmy1234]

cognostechie - can you pls give an example of how you can use a replace function in the sql to avoid curly brackets }....because once you put the curly brackets }, the sql does not validate?

[cognostechie]

That's exactly why you have to replace the curly brackets with another character. If you put the curly brackets inside a replace function then it will validate. I don't know what your SQL is like but consider this:

select
  item_no,
  claim_no,
  replace([Customer_ID]  ,'{' ,'@' )
from .....

So if the Customer_ID = A10{23, it will show A10@23

It will replace the curly bracket with @. If it doesn't work in the filter then create a Data Item with the replace function and then use that data item in the filter.

By the way, I hope you are writing the SQL only for the filter and not writing a custom SQL for the entire report because I do not support the practice of writing a custom SQL in Report Studio. It defeats the purpose of creating a model and is also bad for future. Cognos detects the database and then generates the SQL using the functions and syntax that is acceptable to the database. By writing your own SQL, you will be using functions specific to your current DB which will fail if your company decides to use another database in future.