How to use LIKE operator in BO Custom Query?

[Former Member]

Hi Experts,

I am simply dragging in a field say Project Number from an Universe.
Where filtering on Project Number will be taken from another query based on excel.

i.e the inputs for filter in the query on universe is taken from an query based on excel.

Proj Numbers coming from the database is like:

1234.15

1234.16

8979.16

5564.15

Where ( .15 or .16 or .17 ) are are year extensions of a project Number.

However the excel has values of Project Number in the form of:

1234

5564

Users want to filter without the (.year) extension.

If i use "IN"- result from another query : it will not match the required project and hence the result will be blank.

I tried to use "matches" option in the query filter but "matches" doesn't allow to take results from another query(excel in this case)

I also tried to write a custom sql query over the standard code generated by the query:

Standard code: SELECT DB.PROJECTID ........(from and join conditions)................
AND BD.PROJECTID IN @dpvalue('A', DP2.DO0)

( @dpvalue('A', DP2.DO0) - this is the value coming from excel)

Q. Can I use LIKE operator here in place of "IN"? When I am using that I am getting error.

Q. Is there any way we can address this?

[amitrathi239]

why are you not creating new object in universe without decimal places?

other you can try in custom sql

SELECT DB.PROJECTID ........(from and join conditions)................
AND LEFT(BD.PROJECTID,4) IN @dpvalue('A', DP2.DO0)

[Former Member]

Hi Amit,

I dont have access to Universe and if that is the only option then I have to inform to them to provide me that new Project number without .year extension.

I cannot use Left operator here because the length of the Project number is not fixed :
it can also be 12.CRE.16 or 10.FRT.15 and so on.

[amitrathi239]

Then only option is to create new universe object and then use.

[Former Member]

Thanks Amit