How to combine two cursors in DB2

Question

I have two cursors as below:

EXEC SQL                                      
     DECLARE C1 CURSOR FOR               
     SELECT  DISTINCT FIELD_1                  
     FROM    TABLE                  
     WHERE   FIELD_2 BETWEEN :MAX_DATE
                        AND '9999-12-31'      
           AND   FIELD_3 =  :VALUE 
     WITH UR                                  
     FOR FETCH ONLY                           
END-EXEC

EXEC SQL                                      
     DECLARE C2 CURSOR FOR               
     SELECT  DISTINCT FIELD_1                  
     FROM    TABLE                  
     WHERE   FIELD_2 BETWEEN :MIN_DATE
                        AND '9999-12-31'      
           AND   FIELD_3 =  :VALUE 
     WITH UR                                  
     FOR FETCH ONLY                           
END-EXEC

Both the cursors are pointing to same table. How can I combine the two cursors? Please let me know a way to combine the two tables.

What do you mean when you say "cobine" the two cursors? Do you want the union of both result sets? Or the intersection? Or something else? — piet.t, Aug 02 '17 at 06:57
It depends on what you want to do. You could combine the WHERE clause. — data_henrik, Aug 02 '17 at 10:01

score 0 · Answer 1 · answered Aug 02 '17 at 10:04

0

If you just want to combine the predicates of both cursors, of course you could do. Combine the WHERE clauses:

EXEC SQL                                      
     DECLARE C1 CURSOR FOR               
     SELECT  DISTINCT FIELD_1                  
     FROM    TABLE                  
     WHERE   FIELD_2 BETWEEN :MIN_DATE
                        AND :MAX_DATE
           AND   FIELD_3 =  :VALUE 
     WITH UR                                  
     FOR FETCH ONLY                           
END-EXEC

Your example in the question had both min and max values in a range with 9999-12-31 which doesn't make sense to me.

answered Aug 02 '17 at 10:04

data_henrik

16,724
2
28
49

'DISTINCT' is taking too long time for execution. Can we use anything else instead of 'DISTINCT' so that we can save CPU time and Cost. – Sunitha S Aug 09 '17 at 07:31
i have a cursor like this with 'DISTINCT' clause. This cursor is taking more time to execute in Open Cursor statement. please tell me a way to optimize. Can i use 'ORDER BY' or 'GROUP BY' here – Sunitha S Aug 09 '17 at 08:37
EXEC SQL DECLARE CSR CURSOR FOR SELECT DISTINCT A.FIELD1 ,C.FIELD2 FROM TABLE1 A ,TABLE2 B ,TABLE3 C WHERE A.FIELD3= :VALUE.... – Sunitha S Aug 09 '17 at 08:39

How to combine two cursors in DB2

1 Answers1