CODEWITHSUNDEEP
sqlselectinformix
MD128
MD128

Reputation: 491

Informix SQL 11.5 Attach the result of one select to another select by columns

i have one select:

select c1,c2,c3 from table1 where cond1

--result:

  a  b  c
  d  e  f
  g  h  i

and another select is

select m2 from table2 where cond2

--result:

 x
 y
 z

What combination of the two can I achieve with this output?

select m2 from table2 where cond2 **COMBIN**  select c1,c2,c3 from table1 where cond1

--result

x  a  b  c
y  d  e  f
z  d  e  f

of course, union and join didn't as COMBIN work.

Upvotes: 1

Views: 404

Answers (2)

marcothesane
marcothesane

Reputation: 6749

Well - let's handcraft a common column, using the ROW_NUMBER() OLAP function, and join by that ...

WITH
-- your input
table1(c1,c2,c3) AS (
          SELECT 'a','b','c'
UNION ALL SELECT 'd','e','f'
UNION ALL SELECT 'g','h','i'
)
,
-- your input
table2(m2) AS (
          SELECT 'x'
UNION ALL SELECT 'y'
UNION ALL SELECT 'z'
)
-- real query starts here, replace "," with "WITH"
,
tb1_w_id AS (
  SELECT
    ROW_NUMBER() OVER(ORDER BY c1) AS id
  , *
  FROM table1
  WHERE true
)
,
tb2_w_id AS (
  SELECT
    ROW_NUMBER() OVER(ORDER BY m2) AS id
  , *
  FROM table2
  WHERE true
)
SELECT
  m2
, c1
, c2
, c3
FROM tb1_w_id
JOIN tb2_w_id ON tb1_w_id.id = tb2_w_id.id
;
-- out  m2 | c1 | c2 | c3 
-- out ----+----+----+----
-- out  x  | a  | b  | c
-- out  y  | d  | e  | f
-- out  z  | g  | h  | i

Upvotes: 0

jsagrera
jsagrera

Reputation: 2013

You could do it with an SPL/Function. Something like:

--drop procedure p1 ();
create procedure p1 () returning char AS t2_c1,char AS t1_c1,char AS t1_c2,char AS t1_c3;

   define t1_c1 char;
   define t1_c2 char;
   define t1_c3 char;  
   define t2_c1 char;
      
   prepare t1_id from "select c1,c2,c3 from t1";
   prepare t2_id from "select c1 from t2";
   declare t1_cur cursor for t1_id;
   declare t2_cur cursor for t2_id;
   open t1_cur;
   open t2_cur;
   
   while (1=1)   
    fetch t1_cur into t1_c1,t1_c2,t1_c3; 
    if (sqlcode == 100)  then
           exit;
    end if 
    fetch t2_cur into t2_c1; 
    if (sqlcode == 100)  then
           exit;
    end if      
    return t2_c1,t1_c1,t1_c2,t1_c3 with resume;    
   end while   

   close t1_cur;   
   close t2_cur;
   free t1_cur ;   
   free t2_cur ;
   free t1_id ;
   free t2_id ;
end procedure;

execute procedure p1();

Which should give you:

D:\Infx\ids1410>dbaccess stores7 p
Database selected.
Routine dropped.
Routine created.

t2_c1 t1_c1 t1_c2 t1_c3

x     a     b     c
y     d     e     f
z     g     h     i

3 row(s) retrieved.
Database closed.
D:\Infx\ids1410>

but it would be a lot simpler if you have a common column ;)

Upvotes: 2

Related Questions