Need help with query

[Vick]

Can anyone pleaset tell me how do I select only the reportname(i.e.DW Client Search Contact Events) from the below text.

CAMID("Default:u:authid=2558584710")/folder[@name="Cal's Folder"]/report[@name='DW Client Search Contact Events']


Any help is appreciated.


Thanks,
Vick.

[blom0344]

Approximately:

substring([TEXTFIELD],(position('report[@name=',[TEXTFIELD])+14))

The Position function is used to find the start point for the substring function. You need to have a search string that is unique for the entire text (hence the search for  << report[@name= >>  ) to be able to start at the right point. Adding something like 14 gets you to the right starting point.

The example will fetch everything to the end of the textfield including the last quote.

If you want to omit the quote then the whole calculation will be a bit more extensive , like:


1. Create a dataitem that holds the entire text minus the last quote:
substring([TEXTFIELD],1,character_length([TEXTFIELD])-1)

2. Use this dataitem as input to the expression above

[Vick]

Thanks for the reply, I used following in the dataitem and Data item validates fine.

substr([REPORT NAME],instr([REPORT NAME],'@',1,1)+4,length([REPORT NAME])

The moment I drag this data item into list as one of the column, my report throws this error.

Initially, in data source type(s) 'OL', function 'fn:instr' is not supported in 'RelationalQueryProvider'. After decomposition, in data source type(s) 'OL', function 'fn:instr' is not supported in 'RelationalQueryProvider'.

I guess my database does not support instr function, does anybody know of similar function in Oracle to accomplish this.

Thanks,
Vick

[blom0344]

I think you're mixing things up a bit.  OL indicates  OLE-DB which you would use with SQL server. Oracle would imply using  OR in your connection setting, otherwise you will not be able to use Oracle specific functions.

However, the first aim should be using non-database specific functions , cause if you can manage with Cognos type functions, then you are on much safer ground.  (hence my example..)
Furthermore, do not expect the world from the validate option. It checks syntax allright, but it cannot foresee runtime incompatabilities..