Clever Geek Handbook

Choosing hardcoded values ​​in Informix DB

I need to select hardcoded values ​​in one column so I can join them with a table in Informix DB. Therefore, I try to do something like this in different ways:

select a from ( values (1), (2), (3) ) ;

And I expect to get results:

1
2
3

I think in another DB this or some other options I tried will return values. However, this does not work in Informix.

Can anyone suggest a solution that works in Informix please?

+3


source to share


2 answers


Informix requires an actual request. I think this will work:



select a
from (select 1 as a from systables where tabid = 1 union all
      select 2 as a from systables where tabid = 1 union all
      select 3 as a from systables where tabid = 1
     ) t;
+3


source


While what Gordon Linoff suggests will certainly work, there are also more compact notation available using Informix-specific syntax.

For example:

SELECT a
  FROM TABLE(SET{1, 2, 3}) AS t(a)


This will generate a list of integers rather happily (and briefly). You can use LIST or MULTISET instead of SET. MULTISET can have duplicate elements, unlike SET; LIST preserves order and also allows for repeating.

Very often you don't find that the order isn't preserved with simple values ​​- just a few items in the list. Ordering is not guaranteed for SET or MULTISET; if order matters, use LIST.

For information on this, see the IBM Informix 12.10 tutorial under Collection Constructors . No, it's not obvious how you get to it - I started with SELECT

, then FROM

, then "Select from a collection variable" and from there to "Expression"; I spent a few seconds looking at it stupidly, then looked at "constructor expressions" and thus "collector constructors".

+4


source






More articles:


All Articles