Search code examples
db2ibm-midrange

DB2 regular expression sql not working in green screen

Facing a weird issue where the below SQL query returns expected results in "Run SQL scripts" gui but whereas in green screen STRSQL, it just doesn't return the right values. Meaning, I would not be able to embed this SQL properly in an SQLRPGLE program.

SELECT wrktitle FROM IACDTAA.IACWTL WHERE workkey = 57944682 and        
REGEXP_LIKE(TRIM(WRKTITLE), '^[0-9]{4}.*')

Result from RUN SQL gui is this:

0374 ARTX LU 079 PUBLDTE=20120101 LA2=B C PD A

But when I run the below in STRSQL,

SELECT wrktitle FROM IACDTAA.IACWTL WHERE workkey = 57944682 and
REGEXP_LIKE(TRIM(WRKTITLE), '^[0-9]{6}.*')

I get the below output, which is incorrect !

 0374 ARTX LU 079 PUBLDTE=20120101 LA2=B C PD A
Solution

  • Don't assume that because STRSQL gives incorrect output that RPG will, too. STRSQL is very different to the SQL precompiler / RPG compiler / RPG runtime combination.

    Please try it in an RPG program.