Problem with using wildcard in Input

In the livereport I have a SQL lets say Select * from dtree where name like %1

I selected input parameter as string. Now if I am running the livereport with the input %ABC the SQL its creating like

select * from dtree where name like '%ABC' which is correct but if I put the input parameter value like %1234 it creating SQL like

select * from dtree where name like '%234'

I am not sure when I have a number after % in the input why its truncating the first character.

Find more posts tagged with

Comments

Colin_Schmidt

For anything other than "%1", you need two "%%".

Colin J. Schmidt

Opentext Content Server Support Admin

colin.schmidt@mwv.com
+1 804.444 5012 (Direct)

MWV
501 South 5th Street
Richmond, VA 23219-0501
mwv.com

On Tue, Jul 8, 2014 at 1:19 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Problem with using wildcard in Input Posted byani.kundu@dvn.com (Kundu, Ani)On 07/08/2014 01:17 PM

In the livereport I have a SQL lets say Select * from dtree where name like %1 I selected input parameter as string. Now if I am running the livereport with the input %ABC the SQL its creating like select * from dtree where name like '%ABC' which is correct but if I put the input parameter value like %1234 it creating SQL like select * from dtree where name like '%234' I am not sure when I have a number after % in the input why its truncating the first character.
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from MeadWestvaco Corporation or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

Ani_Kundu

I pass the input parameter %%ABC then Livelreport creating SQL like -> select * from dtree where name like '%%ABC'

If I pass %%1234 as input parameter the livereport creating SQL like ->select * from dtree where name like '%%234'

So its taking out the fist character if I have % follow by number as input parameter

Bhupinder_Singh

What if you pass the input parameter value \%1234

- Bhupinder

Ani_Kundu

I pass the input \%1234

Got the SQL like ->select * from dtree where name like '\%234'

so its doing the same thing removing first number after %

Riten

I wonder could you not pass value without “%” sign in it and build the SQL that includes “%”. For example, your following query would look like

Select * from DTRee where name like ‘%’ + %1 (SQL Server style)

And you would pass 1234.

Thanks,

Riten

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, July 08, 2014 4:37 PM
To: eLink Recipient
Subject: Problem with using wildcard in Input

Problem with using wildcard in Input

Posted byani.kundu@dvn.com (Kundu, Ani) On 07/08/2014 04:31 PM

I pass the input \%1234

Got the SQL like ->select * from dtree where name like '\%234'

so its doing the same thing removing first number after %

[To post a comment, use the normal reply function]
Topic:	Problem with using wildcard in Input
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

CBancroft

The most likely cause is that since it is in a LiveReport and %1 is equal to user input 1,

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, July 08, 2014 3:58 PM
To: eLink Recipient
Subject: Problem with using wildcard in Input

Problem with using wildcard in Input

Posted byani.kundu@dvn.com (Kundu, Ani) On 07/08/2014 03:53 PM

I pass the input parameter %%ABC then Livelreport creating SQL like -> select * from dtree where name like '%%ABC'

If I pass %%1234 as input parameter the livereport creating SQL like ->select * from dtree where name like '%%234'

So its taking out the fist character if I have % follow by number as input parameter

[To post a comment, use the normal reply function]
Topic:	Problem with using wildcard in Input
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Ani_Kundu

I can not do like that because I don't know where user will put wildcard at the time of search. If I hard code % at the begining or end will get more result which we don't want. I think it's a bug in Opentext livereport.

Lindsay_Davies

Hi Ani,

What version of Content Server are you using?

Are you using an Oracle database or a SQL Server database?

SQL behaviour sometimes varies according to the RDBMS in use.

In the examples I list below, I have been verifying behaviour against CS10.0 and an Oracle database, but it should be similar in other releases and SQL Server.

In LiveReports, parameters that are passed in are used in the SQL statement as variables called %1, %2 etc.

As defined in the documentation, any literal % character is escaped by doubling it.

When it comes to a use passing in a text string, that is contained within the assigned variable, so does any characters in it do not need to be escaped.

Let’s take your original sample statement…

Select * from dtree where name like %1

and assume items exist in the DTree table with the following names

Folder

Folder 103

Folder 765

Folder3

New Folder

Our imaginary user is looking for items with ‘Folder’ somewhere in the name.

Running the report gives a screen that has a label and a field for the string to be entered.

If you enter Folder

it will only return

Folder

as there is only (exact) match, because the string has no wild card.

If you enter %Folder

It will return

Folder

New Folder

If you enter Folder%

It will return

Folder

Folder 103

Folder 765

Folder3

Note, it does not return New Folder.

If you enter %Folder%

then it will return all the rows of our test dataset.

It is not necessary to adjust the variable or SQL statement to accommodate this.

For an end user who may be familiar with writing SQL statements, they need to be aware that they must provide some input and also that an asterisk is not valid.

No characters in the string becomes a null string, so no rows match as Content Server does not allow such an entry.

If no filter is required then specify a single % as a wild card.

You have the opportunity to give clues as to behaviour of your report in the name label or description.

This behaviour of ‘%’ character wildcard and not ‘*’ is the same as you would see when constructing a SQL statement in a native SQL application like Oracle’s SQL*Plus or SQL Server Management Studio’s Query Manager interface.

Going back to your symptoms, I would say they are happening because you are simulating the variable in the SQL statement instead of running the report and supplying the string from the GUI screen.

When you specify a string in the SQL statement any % character followed by a digit will be replaced as a variable, as we have already seen discussed in this topic.

The end user running the report will not be able to do this.

Finally, there are two wildcard characters, % (percent) and _ (underscore).

Percent matches zero or more characters.

Underscore matches only 1 character, so specifying _Folder would only return New Folder.

It would not return Folder because it does not have a character before the F.

If you want to find a special character, you can add an escape definition clause to your where statement (this is ANSI SQL so works in Oracle and SQL Server).

E.g. Select * from dtree where name like %1 escape '[‘

If I also had these items in DTree

Folder_xyz

Folder %ages

then I can find the first with Folder[_% - escaping the underscore and adding % as a wildcard for 0 or more trailing characters.

I can find the second with Folder [%ages - escaping the % character and then adding the rest of the string (or I could add a wildcard).

I hope this clears up any questions you might have.

Regards

Lindsay

European Escalation Team

OpenText UK

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2014 July 08, Tue 22:03
To: eLink Recipient
Subject: RE Problem with using wildcard in Input

RE Problem with using wildcard in Input

Posted byani.kundu@dvn.com (Kundu, Ani) On 07/08/2014 04:59 PM

[To post a comment, use the normal reply function]
Topic:	Problem with using wildcard in Input
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

sıɹɥɔ

Quoted Ani Kundu on 07/09/2014 10:35 PM:

Hi Ani,

I agree that for the specific situation you're describing this is indeed a bug. Please contact Support for confirmation and potential resolution. (You may want to mention the module "LiveReport Extensions", as that's where I think the issue originates.)

Thanks,

-chris