Informix

 View Only

  • 1.  from SETs to Regular tables

    Posted Fri April 30, 2021 10:15 AM
    Edited by System Fri January 20, 2023 04:17 PM
    I want to convert the following table:

    1 SET{10,11} SET{20,21}
    2 SET{15} SET{30,31}
    .
    .
    .

    into this:

    1 10 r
    1 11 r
    1 20 a
    1 21 a
    2 15 r
    2 30 a
    2 31 a
    .
    .
    .

    WITH ttt (sq, rejectedPinSet, acceptedPinSet) AS
      (SELECT 1, SET{10,11}, SET{20,21} UNION ALL
       SELECT 2, SET{15}, SET{30,31}
      ) SELECT * FROM ttt;
    The following fails because ord+1 should come from "test" table but "test" table is not mentioned in the second UNION portion  
    WITH test(ord, PIN, ra) AS
      ((SELECT 1, *, 'r' FROM TABLE ((SELECT rejectedPinSet FROM ttt WHERE sq = 1)) AS rej(pin) UNION
        SELECT 1, *, 'a' FROM TABLE ((SELECT acceptedPinSet FROM ttt WHERE sq = 1)) AS acp(pin)
       ) UNION
        (SELECT ord+1, *, 'r' FROM TABLE ((SELECT rejectedPinSet FROM ttt WHERE sq = ord+1)) AS rej(pin) UNION
         SELECT ord+1, *, 'a' FROM TABLE ((SELECT acceptedPinSet FROM ttt WHERE sq = ord+1)) AS acp(pin) )
       ) SELECT * FROM test


    In the following construct I replaced ord+1 for (SELECT ord+1 FROM test) but fails with error -26088:
    WITH test(ord, PIN, ra) AS 
    ((SELECT 1, pin, 'r' FROM TABLE ((SELECT rejectedPinSet FROM ttt WHERE sq = 1)) AS rej(pin) UNION 
      SELECT 1, pin, 'a' FROM TABLE ((SELECT acceptedPinSet FROM ttt WHERE sq = 1)) AS acp(pin)) 
    UNION ALL 
    (SELECT (SELECT ord+1 FROM test), pin, 'r' FROM TABLE ((SELECT rejectedPinSet FROM ttt WHERE sq = (SELECT ord+1 FROM test))) AS rej(pin) UNION 
    SELECT (SELECT ord+1 FROM test), pin, 'a' FROM TABLE ((SELECT acceptedPinSet FROM ttt WHERE sq = (SELECT ord+1 FROM test))) AS acp(pin)) 
    ) SELECT * FROM test

    The following test fails too:
    SELECT sq, pin
    FROM ttt AS t1
    JOIN TABLE ((SELECT rejectedPinSet FROM ttt WHERE sq = t1.sq)) AS rej(pin)


    ------------------------------
    Zambrano, Hugo
    Informix DBA
    Ottawa Police
    Ottawa, ON
    (613)236-1222, 5575
    ------------------------------
    #Informix


  • 2.  RE: from SETs to Regular tables

    IBM Champion
    Posted Fri April 30, 2021 01:30 PM
    Like this: 
    
dbaccess testdb -
    > select 1,*,'r' from table( set{ 10,11} )
    > union
    > select 1,*,'a' from table( set{ 20,21 } )
    > union  
    > select 2,*,'r' from table( set{15})
    > union  
    > select 2,*,'a' from table( set {30,31})
    > ;

    (constant) unnamed_col_1 (constant)
             1            10 r
             1            11 r
             1            20 a
             1            21 a
             2            15 r
             2            30 a
             2            31 a 7

    row(s) retrieved.

    >

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    
                                                
    
					
                                                    

        
                                                
				
    
                                                
    
                                                    
                                                        
                                                        Original Message
                                                
    
                                                
                                            
    
                                        
    

                                        
                                        

                                    
			
    

                                    
                                
    

                                
                                
    
                                
    
                            
		
    • 
                
                    
  • 
			                                
                                
                                
    
                                    
    
                                        
    
                                            
                                            3.  
                                            RE: from SETs to Regular tables
                                            
                                            
                                            
                                        
    
                                    
    
                                    
    
                                        
    
				
                                                
    
                                                    
    
					
        
        
        
        
        
        
        
            
        
    
				
    

                                                
    
                                            
			
    
                                    
    
                                
    
                                
                                

                                
    
                                    
    
                                        
                                    
    

                                    
    
				

                                        
    
                                            
    
                                                
    
                                                    
    
                                                        Posted Sat May 01, 2021 04:49 PM
    Edited by System Fri January 20, 2023 04:23 PM    
                                                        
                                                    
    
                                                
    
                                            
    
                                            
    
                                                

                                                
                                            
    
                                        
    

                                        
    
                                            
    
                                                Thanks Art,

    That’s the idea, to find a way to programmatically (with SQL statements) be able to do all those UNIONs applied to hundreds of records.

    ------------------------------
    Zambrano, Hugo
    Informix DBA
    Ottawa Police
    Ottawa, ON
    (613)236-1222, 5575
    ------------------------------

    
                                                
    
					
                                                    

        
                                                
				
    
                                                
    
                                                    
                                                        
                                                        Original Message
                                                
    
                                                
                                            
    
                                        
    

                                        
                                        

                                    
			
    

                                    
                                
    

                                
                                
    
                                
    
                            
		
    • 
                
                    
  • 
			                                
                                
                                
    
                                    
    
                                        
    
                                            
                                            4.  
                                            RE: from SETs to Regular tables
                                            
                                            
    
				Best Answer
			
    
                                            
                                        
    
                                    
    
                                    
    
                                        
    
				
                                                
    
                                                    
    
					
        
        
        
        
        
        
        
            
        
    
				
    

                                                
    
                                            
			
    
                                    
    
                                
    
                                
                                

                                
    
                                    
    
                                        
                                    
    

                                    
    
				

                                        
    
                                            
    
                                                
    
                                                    
    
                                                        Posted Fri May 07, 2021 11:57 PM
                                                        
                                                    
    
                                                
    
                                            
    
                                            
    
                                                

                                                
                                            
    
                                        
    

                                        
    
                                            
    
                                                Would something like this work for you ( tested on Informix 14.10FC5 )?

    CREATE TABLE demo01
    (
      id INTEGER,
      col_a SET ( INTEGER NOT NULL ),
      col_b SET ( INTEGER NOT NULL )
    );
    INSERT INTO demo01 VALUES( 1, SET{ 10, 11 }, SET{ 20, 21 } );
    INSERT INTO demo01 VALUES( 2, SET{ 15 }, SET{ 30, 31 } );

    ----------------

    SELECT * FROM demo01;
    id 1
    col_a SET{10 ,11 }
    col_b SET{20 ,21 }

    id 2
    col_a SET{15 }
    col_b SET{30 ,31 }

    ----------------

    SELECT vtab1.id AS ord, vtab2.pin AS pin, 'r' AS pinset FROM demo01 AS vtab1,
    LATERAL
    (
      TABLE( ( SELECT vtab3.col_a FROM demo01 AS vtab3 WHERE vtab1.id = vtab3.id ) ) 
    ) AS vtab2( pin )
    UNION
    SELECT vtab4.id AS ord, vtab5.pin AS pin, 'a' AS pinset FROM demo01 AS vtab4,
    LATERAL
    (
      TABLE( ( SELECT vtab6.col_b FROM demo01 AS vtab6 WHERE vtab4.id = vtab6.id ) ) 
    ) AS vtab5( pin )

    ord pin pinset

    1 10 r
    1 11 r
    1 20 a
    1 21 a
    2 15 r
    2 30 a
    2 31 a


    ------------------------------
    Luis Marques
    ------------------------------

    
                                                
    
					
                                                    

        
                                                
				
    
                                                
    
                                                    
                                                        
                                                        Original Message
                                                
    
                                                
                                            
    
                                        
    

                                        
                                        

                                    
			
    

                                    
                                
    

                                
                                
    
                                
    
                            
		
    • 
                
                    
  • 
			                                
                                
                                
    
                                    
    
                                        
    
                                            
                                            5.  
                                            RE: from SETs to Regular tables
                                            
                                            
                                            
                                        
    
                                    
    
                                    
    
                                        
    
				
                                                
    
                                                    
    
					
        
        
        
        
        
        
        
            
        
    
				
    

                                                
    
                                            
			
    
                                    
    
                                
    
                                
                                

                                
    
                                    
    
                                        
                                    
    

                                    
    
				

                                        
    
                                            
    
                                                
    
                                                    
    
                                                        Posted Mon May 10, 2021 11:13 AM
    Edited by System Fri January 20, 2023 04:16 PM    
                                                        
                                                    
    
                                                
    
                                            
    
                                            
    
                                                

                                                
                                            
    
                                        
    

                                        
    
                                            
    
                                                Gracias Luis, that was beautiful. It does work as required. I thought there was not going to exist an answer but you found it. I did see that LATERAL in the documentation but I could not used properly.

    I just change a little bit the nomenclature for my understanding:

    

     SELECT t1.sq, t2.pin AS pin, 'r' AS ra 
       FROM demo01 AS t1,
       LATERAL (TABLE((SELECT x1.rejectedPinSet FROM demo01 AS x1 WHERE x1.sq = t1.sq))) AS t2(pin)
     UNION
     SELECT t3.sq, t4.pin AS pin, 'a' AS ra 
       FROM demo01 AS t3,
       LATERAL (TABLE((SELECT x2.acceptedPinSet FROM demo01 AS x2 WHERE x2.sq = t3.sq ))) AS t4(pin)
     ORDER BY sq, ra DESC, pin

    Thanks a lot.

    ------------------------------
    Zambrano, Hugo
    Informix DBA
    Ottawa Police
    Ottawa, ON
    (613)236-1222, 5575
    ------------------------------

    
                                                
    
					
                                                    

        
                                                
				
    
                                                
    
                                                    
                                                        
                                                        Original Message
                                                
    
                                                
                                            
    
                                        
    

                                        
                                        

                                    
			
    

                                    
                                
    

                                
                                
    
                                
    
                            
		
    •