sqlcastingrowinformixmultiset

SQL Informix 12.10FC6 - Cast multiple columns of multiset at once, but without creating a user defined type

I'll show what I got working and what I need help, as follows

  1. [works] - Here I'm creating a multiset on the fly, without needing to create a user defined type. SELECT * FROM TABLE(MULTISET{ ROW(100, 'Kline'), ROW(101, 'Smith'), ROW(102, 'Jones'), ROW(103, 'Brown')}) applicant(aplicantid, lastname) INTO TEMP APPLICANT; But it only works because the string names have all a length of 5 characters.

  2. [doesn't work] If I the strings are of different sizes I'll receive "Type %s not found, code -9628" as in: SELECT * FROM TABLE(MULTISET{ ROW(10, 'Software Developer') ,ROW(11, 'Business Analyst') ,ROW(12, 'Data Analyst') })

  3. [inadequate solution] I just got able of overcoming the error in the item 2 by casting each row individually SELECT * FROM TABLE ( MULTISET{ ROW(10, 'Software Developer') :: ROW (x int, y varchar(20) ) , ROW(11, 'Business Analyst') :: ROW (x int, y varchar(20) ) , ROW(12, 'Data Analyst ') :: ROW (x int, y varchar(20) ) } ). This is extremely frustrating to keep repeating the same casting for all rows.

  4. [Example of what I need] I'd need to cast the entire Multiset() at once, or even the table() but without needing to create a user defined type, as I did in the item 3. Example (which doesn't work) = SELECT * FROM TABLE(MULTISET{ ROW(10, 'Software Developer') ,ROW(11, 'Business Analyst') ,ROW(12, 'Data Analyst') }) :: TABLE(MULTISET{ROW(x int, y varchar(20))}) So all the content would be converted at once.

Ps: please the server don't has datablade modules.

Really thanks.

Solution

  • I realise that you've long since moved on, but for the benefit of any one else searching:

    SELECT * FROM TABLE (
        MULTISET{
            ROW(10, 'Software Developer')
            ,ROW(11, 'Business Analyst')
            ,ROW(12, 'Data Analyst')
        }::MULTISET( ROW( x int, y varchar(20) ) not null )
    );

    The multiset{} syntax is a Literal the same as 'software developer' or '7' the MULTISET() syntax is type definition. So given that we're creating a cast, we need to cast to type not to literal (in the same way that 'software developer'::'7' makes no sense but 'software developer'::char(30) does) For the same reason, we drop your TABLE from the cast, as that's part of the virtual table syntax, not part of your type definition.

    Because multiset's don't allow null values, and for some reason we have to tell Informix its own rules, we need to add 'not null' to the row definition (so no null rows, values within the row can be null) inside the multiset.

    The fact that multisets use different brackets for literals and types, while rows use () for both is completely inexplicable to me.