RE: Using regular expression in SQL give error SQLCODE=171 and SQLSTATE=42815

Mike, Nulls could be Jose's issue if his file contains null capable columns.
He didn't specify and he doesn't appear to doing any left joins.

I was also unable to reproduce Jose's issue by passing nulls into the functions. None of the calls returned an error.

values( REGEXP_SUBSTR( CAST( NULL AS VARCHAR(100)),'I[0-9]{5}'));

values( REGEXP_COUNT( CAST( NULL AS VARCHAR(100)),'I[0-9]{5}'));

SELECT * FROM sysibm/sysdummy1 WHERE REGEXP_LIKE( CAST( NULL AS VARCHAR(100)), '$[0-9]{0,5}');


In the cases where you see the system running SQL over a row that you know won't be included in the result set, SQL is probably generating a temporary result set to satisfy the REGEXP call and then joining that to your other tables.

I would suggest using visual explain to help you figure out it is doing and to help fix what is wrong.
Visual Explain is included in the iAccess Client SQL Tool.

Thanks,

Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike Jones
Sent: Monday, February 13, 2017 11:27 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Using regular expression in SQL give error SQLCODE=171 and SQLSTATE=42815

Hi Chris,

I use those REGEXP functions frequently.

I've noticed that they sometimes have issues when you pass a NULL value to them. Try passing IFNULL( PLDESCB, '' ) to them instead of just PLDESCB.

They also seem to get called even when my code gives direction to the SQL engine to not call them for a given row. Granted, in those cases, the values returned by the REGEXP call get discarded because my code is structured to not use the returned value, but I've had to make code changes to guard against passing NULLs even when I've told the SQL engine not to call the the REGEXP function at all for a given row.

I don't know if this is your problem or not, but it is the first thing I'd try.

Mike