sql Regular Expressions with Report Manager

[Loren Watts]

If there are existing Broadcom documentation pages for using sql with Report Manager, it would be great to digest that information.

I'm looking for the specific sql syntax to use Regular Expressions in Report Manager.
I want to use a Regular Expressions character set [DFM] and can't get the syntax in Report Manager sql.

Underscore works but grabs more than I want.

WHERE FULLNAME LIKE '%CK_M%'

I want to limit hits to third characters D or F or M in the four characters CK_M
only hits matching CKDM or CKFM or CKMM

WHERE FULLNAME LIKE '%CK[DFM]M%'

above statement does not error but returns no rows. 
'%CK_M%' as I wrote returns what I want, plus unwanted rows.

[Lucy Zhang]

Hi Loren,

Have you tried to issue the same SQL command from the related database? Does it work?

Generally the Report Manager will just pass the SQL command to the database.

If it works when issued directly from the database and not working from ESP report manager, please open a support case and we will look further.

Thank you,

Lucy

[Loren Watts]

Thank you Lucy.

Using sql against my Oracle table, I must use the Oracle function REGEXP_LIKE. 

Is there documentation for Report Manager SQL syntax? And use of Regular Expressions in SQL?

Function Name    Description
REGEXP_LIKE        Similar to the LIKE operator, but performs regular expression matching instead of simple pattern matching
REGEXP_LIKE(srcstr, pattern [,match_option])

[Lucy Zhang]

Hi Loren,

Let me consult our next level.

Will let you know their response.

Thank you,

Lucy

[Lucy Zhang]

Hi Loren,

Sorry for the late response.

Will you try as below and see if it work?
SELECT FULLNAME
FROM your_table
WHERE REGEXP_LIKE(FULLNAME, '^CK[DFM]M$');

Thank you,

Lucy

[Loren Watts]

Thank you Lucy. That statement brings this error -

Server Message
ESPWSS Error:
ESPWSS69444E DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601,
SQLERRMC=<END-OF-STATEMENT>;WITHIN IS LIKE BETWEEN = ^=
> >= < <= ^< ^> <> != !, DRIVER=3.72.54

[Lucy Zhang]

Hi Loren,

Guess we misunderstood you. We thought you use Oracle database with the Report Server.

Do you use DB2 with our Report Server? If yes, how will you issue the SQL command directly from DB2 database?

The SQL command should have sent to the DB2 and the returned error should be from your DB2 database.

Thank you,

Lucy

[Loren Watts]

Sorry for the confusion. I received confirmation this morning that our CA WA Workstation uses DB2 for Report Manager. Until this research, I was not familiar with REGEXP_LIKE, and first tested it in an Oracle table outside ESP. I personally do not use DB2 sql directly on tables. For this current topic I am using the SQL tab of CA WA Report Manager GUI for the sql statement.

SELECT FULLNAME
FROM ESPRSD2P.ESP_JOB_HIST
WHERE REGEXP_LIKE(FULLNAME, '^CK[DFM]M$');

[Lucy Zhang]

Hi Loren,

I've done search and found following IBM DB2 online doc (for current version) about "REGEXP_LIKE":
https://www.ibm.com/docs/en/db2-for-zos/13?topic=functions-regexp-like

It says it's "passthrough-only and cannot run on Db2 for z/OS® without acceleration."

You may double check with your DB2 admin or IBM support on this, and if the query can be passed to the accelerator in your environment. 

Based on the examples from the above doc link, the SQL can be adjusted to be as below:
SELECT FULLNAME
FROM ESPRSD2P.ESP_JOB_HIST
WHERE REGEXP_LIKE(FULLNAME, '?CK[DFM]M+?','c') = 1

Hope this helps,

Lucy

And if