CODEWITHSUNDEEP
informixsqldatatypesspl
cbeitzell
cbeitzell

Reputation: 21

Informix 11.70.FC8GE - How to pass a LIST data type to a SPL using a SELECT

I saw another question concerning pushing SET data types to c# code, but it didn't really answer what I was trying to do.

I am trying to pass a LIST data type to a SPL using a select. I know in a SPL a LIST can be DEFINED and set as such:

DEFINE a LIST(INTEGER NOT NULL);
LET a = LIST{(SELECT unique b from numberTable)};

I also know that a list can be sent to a SPL if its variable is a LIST.

execute procedure someProc(LIST{1,2,3});

Knowing these two things I want to try to create a LIST from a SELECT for the variable. 

execute procedure someProc(LIST{(SELECT unique b from numberTable)});

So I created a small test case.

create table tab1 (a int);

insert into tab1 values (1);
insert into tab1 values (2);
insert into tab1 values (3);

create procedure retInt(c LIST(INTEGER NOT NULL))
RETURNING INTEGER;

DEFINE b INTEGER;

FOREACH SELECT * INTO b from TABLE(c)
        return b with RESUME;
end FOREACH

end procedure;

-- Works
execute procedure retInt(LIST{1,2,3});

-- Doesn't work
-- returns 284: A subquery has returned not exactly one row.
execute procedure retInt(LIST{(SELECT a from tab1)});

I also tried type cast the select like this

execute procedure retInt((SELECT a from tab1)::LIST(INTEGER NOT NULL));

Needless to say that created a syntax error.

I may be trying to do something that isn't possible. Looking through IBM's documentation I did not find anything, however sometimes I feel that their documentation can be difficult to use.

Any thoughts?

Thank you in advance, Caton

Upvotes: 2

Views: 1726

Answers (1)

Luís Marques
Luís Marques

Reputation: 1451

In Informix you cannot build a LIST directly from a SELECT statement unless the select returns exactly 1 row. I suspect that in the example that you gave the query is returning exactly 1 row and the LIST is created.

Informix won't let you create a LIST from a SELECT that returns multiple rows, but it accepts creating a MULTISET from a SELECT that returns multiple rows, that then can be cast to a LIST.

EXECUTE PROCEDURE retInt((MULTISET(SELECT ITEM a FROM tab1)::LIST(INTEGER NOT NULL)));

The keyword ITEM is required to create a MULTISET of INTEGER values, instead of a multiset of ROW values.

Upvotes: 2

Related Questions