How to pass result of query to function being part of the same?

Question

This is with Informix 11.70.FC6GE on Linux.

Assuming table mytable with column value varchar(16) and a function as following:

create function myfunc(str varchar(16)) returning varchar(16)
  define result varchar(16);

  while (<some-condition>)
    let result = ...;
    return result with resume;
  end while;

end function;

When I do

select * from mytable, table(myfunc(value)) vt(result);

I get

Not implemented yet. [SQL State=IX000, DB Errorcode=-999]

... - :-S.

Doing

select * from mytable, table(myfunc('some literal')) vt(result);

works.

Is there any chance to get this going in the given environment? And if not: To which version of Informix do I need to switch?

I suspect your question refers to the "Edition", which is *Informix Growth Edition V11.70* @JonathanLeffler — alk, Oct 14 '15 at 18:11
I was interested in 11.70 vs 12.10 or one of the many older versions. The edition makes little difference in this context. I'll have to experiment. I suspect the problem is related to doing a correlated query but trying to treat one of the tables as a variable. I don't even have good words to describe it. — Jonathan Leffler, Oct 14 '15 at 18:26
Sorry for the immense delay creating an answer for this question. — Jonathan Leffler, Jul 09 '16 at 17:17

Jonathan Leffler · Answer 1 · 2021-01-09T05:46:49.980

I don't think there's a way to do what you want; I'm sure there isn't an easy way to do something similar.

Setup

Consider a database with two tables: a table of (chemical) elements — the periodic table — and a table of (US) states.

CREATE TABLE elements
(
    atomic_number   INTEGER NOT NULL PRIMARY KEY
                    CHECK (atomic_number > 0 AND atomic_number < 120),
    symbol          CHAR(3) NOT NULL UNIQUE,
    name            CHAR(20) NOT NULL UNIQUE,
    atomic_weight   DECIMAL(8, 4) NOT NULL,
    pt_period       SMALLINT NOT NULL
                    CHECK (pt_period BETWEEN 1 AND 7),
    pt_group        CHAR(2) NOT NULL
                    -- 'L' for Lanthanoids, 'A' for Actinoids
                    CHECK (pt_group IN ('1', '2', 'L', 'A', '3', '4', '5', '6',
                                        '7', '8', '9', '10', '11', '12', '13',
                                        '14', '15', '16', '17', '18')),
    stable          CHAR(1) DEFAULT 'Y' NOT NULL
                    CHECK (stable IN ('Y', 'N'))
);

CREATE TABLE US_States
(
    code    CHAR(2) NOT NULL PRIMARY KEY,
    name    VARCHAR(15) NOT NULL UNIQUE
);

I'll assume you can populate the two tables with the correct data (see Web Elements for the periodic table; the Informix demo database 'stores' has a table state isomorphic to the US_States table used here).

Now consider a procedure states_starting():

CREATE FUNCTION states_starting(initial CHAR(1)) RETURNING VARCHAR(15);

    DEFINE result VARCHAR(15);

    FOREACH SELECT Name
              INTO result
              FROM US_States
              WHERE Code[1] = initial
              ORDER BY Name
        RETURN result WITH RESUME;
     END FOREACH;

END FUNCTION;

Adapting the queries in the question

I was a little surprised that the vt(result) notation worked — but it does, designating a table alias vt and the column name result. Hence, an adaptation of the query that worked is:

SELECT *
  FROM Elements, TABLE(states_starting('M')) vt(result)

This generates the Cartesian product of 118 elements and 8 states with names beginning with 'M' for 944 rows in total. A slightly more reasonable query is:

SELECT *
  FROM Elements JOIN TABLE(states_starting('M')) AS vt(result)
    ON Elements.Symbol[1] = vt.result[1]
 ORDER BY Elements.Atomic_Number

This generates results for 6 elements (Magnesium, Manganese, Meitnerium, Mendelevium, Molybdenum, Moscovium: Mercury doesn't figure because its symbol is Hg, which does not start with an M) and 8 states as before, for 48 rows.

The adaptation of the query that you want to execute looks something like:

SELECT *
  FROM Elements AS e
  JOIN TABLE(states_starting(e.name[1])) AS vt(result)
    ON Elements.Symbol[1] = vt.result[1]
 ORDER BY Elements.Atomic_Number

This doesn't work, however, generating error:

-217: Column (name) not found in any table in the query (or SLV is undefined).

That's not identical to the error in the question; I've not been able to replicate that. But it is symptomatic of the problems this query faces.

The problem is that in the TABLE(…), the name e isn't known, but removing the e. from the argument doesn't change things.

Further, to generate the complete "correct" result, the TABLE(…) expression would have to be evaluated multiple times, once for each separate initial letter. So, you'd need a set of table results from multiple invocations of the function in the TABLE(…) expression with different arguments. But that isn't the way tables work in SQL. They're supposed to be 'fixed'. The constant argument to the function yields a single result set and looks like a table. But trying to invoke it multiple times with different arguments and dealing with the result set would be (at best) tricky — it isn't the way SQL works.

I'm not entirely satisfied with the explanation. The idea I'm trying to express is almost certainly the reason for the queries not working, but I'm not happy that I've explained it well.

I tried number of variations. For example, assuming you create a GROUP_CONCAT aggregate, you might want to try:

SELECT group_concat(states_starting(a))
  FROM (SELECT DISTINCT NAME[1] AS a FROM us_states)
 GROUP BY a

but this generates:

-686: Function (states_starting) has returned more than one row.

I don't think that creating a SET or LIST from the the function result helps.

Testing on Mac OS X 10.11.5 with Informix 12.10.FC6 (and ClientSDK 4.10.FC6, and SQLCMD 90.01 — unrelated to Microsoft's johnny-come-lately of the same name).

How to pass result of query to function being part of the same?

1 Answers1

Setup

Adapting the queries in the question