retiring a result-set from regex_split function
User tried using regex_split() function in a JOIN query but got a "Routine cannot be resolved" error. The solution was to wrap the function call in TABLE(FUNCTION ...) syntax. Additionally, the regex_split function requires the ifxregex DataBlade module to be registered in the database using blademgr, which may not happen automatically in some IDS versions.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I have a list of integers stored in a column "1,2,3,4,5,6,7". Using execute
function regex_split('1,2,3,4,5', ',',) I am getting the output as below
1
2
3
4
5
I am using IDS 12.X
e.g. -
Column user_list has data as = 1,2,3,4,5
select name from user e
inner join ( select regex_split(user_list,,) userid from usergroup) eul on
where cast(eul.userid as long) = e.userid and groupid = 100
Now, my question is, can I use this regex_split function as a inline query,
where the output is alias to a table and join with other tables? I am not able
to get this working.
This, is supported in other DBMS platforms, so I am expecting this should be
supported in Informix as well.
Any suggestion?
Thanks,
Krishna
Hi Krishna.
See alternate solutions below using "TABLE(FUNCTION ...)".
Regards,
Doug Lawry
-- Set up test:
{
DROP TABLE user;
DROP TABLE usergroup;}
CREATE TABLE user (userid INT, name VARCHAR(32));
INSERT INTO user VALUES (1, 'anne');
INSERT INTO user VALUES (2, 'dave');
INSERT INTO user VALUES (3, 'john');
CREATE TABLE usergroup (groupid INT, user_list VARCHAR(32));
INSERT INTO usergroup VALUES (100, '1,2');
-- Solution 1 (least code):
SELECT name
FROM
user AS e
INNER JOIN
TABLE (
FUNCTION regex_split (
(
SELECT user_list
FROM usergroup
WHERE groupid = 100
),
','
)
) AS eul (userid)
ON eul.userid = e.userid;
{
Results:
anne
dave
}
-- Solution 2 (simplest reuse):
{
DROP FUNCTION sp_group_members;
DROP VIEW view_group_members;}
CREATE FUNCTION sp_group_members()
RETURNING INT AS groupid, INT AS userid;
DEFINE v_groupid INT;
DEFINE v_userid INT;
DEFINE v_user_list LIKE usergroup.user_list;
FOREACH
SELECT groupid, user_list
INTO v_groupid, v_user_list
FROM usergroup
FOREACH
SELECT *
INTO v_userid
FROM TABLE(FUNCTION regex_split(v_user_list,','))
RETURN v_groupid, v_userid WITH RESUME;
END FOREACH
END FOREACH
END FUNCTION;
CREATE VIEW view_group_members (groupid, userid) AS
SELECT * FROM TABLE(FUNCTION sp_group_members());
SELECT name
FROM view_group_members AS eul
INNER JOIN user AS e ON eul.userid = e.userid
WHERE groupid = 100;
{
Results:
anne
dave
}
-- Solution 3: normalize your tables!
Anyone know how to post here with indentation and line spacing preserved?!
Thanks Doug, for your inputs.
I am looking for solution-1 which best works for my use case. When I am trying
to tin the function, regex_split() function, I am getting error as
674: Routine (regex_split) can not be resolved.
http://www-01.ibm.com/support/docview.wss?uid=swg21610013
https://www-01.ibm.com/support/docview.wss?uid=swg21573829
When I checked online for details, they say, this
i) function may miss out permission (I use Informix) user.
ii) function is not present,
but I was able to run the function two days back, with example
EXECUTE FUNCTION regex_split('1,2,3,4,5', ',')
Is there something I am missing? any extensions or permission?
BTW, I am pretty new to Informix, mostly worked in PostgreSQL, DB2-UDB,
Sybase, MSSQL and some Oracle.
Appreciate your inputs.
Thanks,
Krishna
Hi Krishna, regexp have been implemented starting at IDS 12.10 xC8, AFAIR. You won't have them on earlier versions. About this topic, my memory also tells me that there has been a very presentation on this topic at IIUG COnference 2015 or 2016. Those presentations are available on the IIUG Website. Not sure where, please bear with me
Reply sent to Krishna by email last week:
You probably just need to do this replacing "test" with your database name:
$ echo register ifxregex.1.00 test | blademgrinformix>Register module ifxregex.1.00 into database test? [Y/n]
Registering DataBlade module... (may take a while).
DataBlade ifxregex.1.00 was successfully registered in database test.
informix>Disconnecting...
Some datablades are registered automatically or dynamically, but this one
didnt for me on IDS 12.10.FC11, so I had to do the above.