SQL Query Prob

[hbkanderi]

Hi.,

In my DB2 table contain 20 rows I need first 10 rows & last 5 rows (1 to 10 & 16 to 20) I need to skip 11 to 15 rows. Pls any one give me the SQL Query for this problem

[Veera]

hbkanderi

There is separate FORUM for DB2 where you can post your DB2 QUERIES but you have posted in Enterprise COBOL, going forward please take care.

Regarding your question,

I am not sure if we can do what ever you have asked for in one PARSE with DB2.

Well but there are ways by which we can achieve what you have asked for.

Approach1:
************

1--> FETCH FIRST n ROWS ONLY -> Using this you can get first 10 rows. where n=10 this time.

2--> Now use the same Query and reverse the order by i.e. use DESC and then again select the FETCH FIRST n ROWS ONLY , where n=5 this time.

SELECT * FROM <<TABLE NAME>> ORDER BY <<Primary Key>> DESC FETCH FIRST 5 ROWS ONLY;; ----> last 5 rows

But for this you need to PARSE the DB2 2 times.

There may be another efficient means to achieve this, lets see if we get few more responses.


Approach2: Scrollable cursor: -> If not Approach1 then Try this approach
********* ***************

Scroallable cursor working with keywords should help us.

Scrollable cursors allow developers to move through the results of a query in multiple ways.

The following key words are supported when fetching data from a scrollable cursor:

NEXT - will FETCH the next row, the same way that the pre-V7 FETCH statement functioned

PRIOR - will FETCH the previous row

FIRST - will FETCH the first row in the results set

LAST - will FETCH the last row in the results set

CURRENT - will re-FETCH the current row from the result set

BEFORE - positions the cursor before the first row of the results set

AFTER - positions the cursor after the last row of the results set

ABSOLUTE n - will FETCH the row that is n rows away from the first row in the results set

RELATIVE n - will FETCH the row that is n rows away from the last row fetched

For both ABSOLUTE and RELATIVE, the number n must be an integer. It can be either a positive

or a negative number, and it can be represented as a numeric constant or as a host variable.

All of the FETCH options for scrollable cursors also reposition the cursor before fetching

the data. For example, consider the following cursor logic:


DECLARE csr1 SENSITIVE STATIC SCROLL CURSOR

FOR SELECT FIRSTNAME, LASTNME

FROM DSN8710.EMP

ORDER BY LASTNME;

OPEN csr1;

FETCH LAST csr1 INTO :FN, :LN;

NOTE : MOSTLY RELATIVE n IS THE KEY WORD WHICH YOU SHOULD USE.

P.S : I donot have DB2 in my system otherwise i wud have run a test and confirmed the results.well lets us know if any of the above approachs worked for you.

Thanks,
Veera.

[DavidatK]

Hbkanderi,

Also remember, that DB2, in theory, doesn?t maintain the concept of physical 1st, 2nd, 3rd? last records. It?s not like a flat file. When you ?fetch first n rows only? you get the first n rows that DB2 gives you, in the easiest access to the data for DB2. It is not necessarily the first n rows inserted into the table. DB2 is a relational database, and you should have some idea of the ?order? you want the rows returned in.

That said, Veera is probably right, that it cannot, at least easily, be done in a single SQL, at least in V7. I believe I?ve read somewhere, that what you want to do, may be possible in V9 and maybe V8 with the enhancements that have been made to those versions.

In the sorted ?order? of the table that you want the 1-10, and 15-20 rows, do the columns in the ?order? uniquely identify the row, without duplicates? We may be able to use this to our advantage if so.

Also, what version of DB2 are you using?

Though I am new to this forum, I have been a member of other forums for some time and know many of the members here and recognize the high degree of talent in them.

Also, academic problems are, many times, much different than business problems, but usually much more fun to solve.

Keep posting, put us to the test, and get your class mates to post also. We love the opportunity to help.

Dave

[DavidatK]

Hbkanderi,

Well, I had to think about this for awhile, and am making some assumptions, which is probably a bad thing to do. I?m going to assume there is a unique key on the table and you want the first 10 key values and the last 5 key values.

I created a temporary table and inserted rows into it. ?N? will be the unique key and ?NM? the name of the key value. As you can see, the values were inserted in random order.

Code: Select all

  DECLARE GLOBAL TEMPORARY TABLE TEMP_TBL       
         (N                DEC(6)               
         ,NM              CHAR(20)              
          )                                     
  ;                                                                                                   
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (1, 'ONE');          
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (2, 'TWO');          
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (10, 'TEN');         
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (15, 'FIFTEEN');     
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (25, 'TWENTY FIVE'); 
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (12, 'TWELVE');      
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (100, 'ONE HUNDRED');
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (99, 'NINETY NINE'); 
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (51, 'FIFTY ONE');   
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (11, 'ELEVEN');      
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (6, 'SIX');          
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (16, 'SIXTEEN');     
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (8, 'EIGHT');        
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (9, 'NINE');         
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (34, 'THIRTY FOUR'); 
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (21, 'TWENTY ONE');  
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (7, 'SEVEN');        
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (88, 'EIGHTY EIGHT');
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (4, 'FOUR');         
  INSERT INTO SESSION.TEMP_TBL (N, NM) VALUES (26, 'TWENTY SIX');   
--                                                             
  SELECT *                                                     
    FROM SESSION.TEMP_TBL                                      
   ORDER BY N                                                  
  ;                                                            
  SELECT *                                                     
    FROM SESSION.TEMP_TBL                                      
   WHERE N <=                                                  
     &#40;SELECT N                                                 
        FROM SESSION.TEMP_TBL   TT1                            
       WHERE &#40;10 - 1&#41; IN                 -- < 10TH LOWEST N    
         &#40;SELECT COUNT&#40;*&#41;                                      
            FROM SESSION.TEMP_TBL   TT2                        
           WHERE TT1.N > TT2.N                                 
          &#41;                                                    
       &#41;                                                       
   OR N >=                                                   
     &#40;SELECT N                                               
        FROM SESSION.TEMP_TBL   TT1                          
       WHERE &#40;5 - 1&#41; IN                  -- < 5TH HIGHTEST N 
         &#40;SELECT COUNT&#40;*&#41;                                    
            FROM SESSION.TEMP_TBL   TT2                      
           WHERE TT1.N < TT2.N                               
          &#41;                                                  
       &#41;                                                     
   ORDER BY N                                                
  ;                                                          

The result of the first SELECT above is:

Code: Select all

    +----------------------------------+ 
    |     N     |          NM          | 
    +----------------------------------+ 
  1_|       1   | ONE                  | 
  2_|       2   | TWO                  | 
  3_|       4   | FOUR                 | 
  4_|       6   | SIX                  | 
  5_|       7   | SEVEN                | 
  6_|       8   | EIGHT                | 
  7_|       9   | NINE                 | 
  8_|      10   | TEN                  | 
  9_|      11   | ELEVEN               | 
 10_|      12   | TWELVE               | 
 11_|      15   | FIFTEEN              | 
 12_|      16   | SIXTEEN              | 
 13_|      21   | TWENTY ONE           | 
 14_|      25   | TWENTY FIVE          | 
 15_|      26   | TWENTY SIX           | 
 16_|      34   | THIRTY FOUR          | 
 17_|      51   | FIFTY ONE            | 
 18_|      88   | EIGHTY EIGHT         | 
 19_|      99   | NINETY NINE          |
 20_|     100   | ONE HUNDRED          |
    +----------------------------------+
 

The result of the second SELECT above is:

Code: Select all

    +----------------------------------+
    |     N     |          NM          |
    +----------------------------------+
  1_|       1   | ONE                  |
  2_|       2   | TWO                  |
  3_|       4   | FOUR                 |
  4_|       6   | SIX                  |
  5_|       7   | SEVEN                |
  6_|       8   | EIGHT                |
  7_|       9   | NINE                 |
  8_|      10   | TEN                  |
  9_|      11   | ELEVEN               |
 10_|      12   | TWELVE               |
 11_|      34   | THIRTY FOUR          |
 12_|      51   | FIFTY ONE            |
 13_|      88   | EIGHTY EIGHT         |
 14_|      99   | NINETY NINE          |
 15_|     100   | ONE HUNDRED          |
    +----------------------------------+

Hope this helps.

Dave



Dave

[Veera]

DavidatK,

Well it slipped of my mind the co-related sub-query did the trick , always
handy for recursive table processing.

But i have few questions regarding your query DavidatK

1-> The query you have mentioned is a CURSOR or SINGLETON select ??

What i am assuming is since you are using the co-related query
foreach value of N from TT1 it should yeild multiple values
from TT2.

Say when the value of N = 3

the results i am aniticpating is

| N | NM |
+----------------------------------+
1_| 1 | ONE |
2_| 2 | TWO |

Likey for each value of N we should get more than one record as a result.

or

Is it since you are using a singleton select the latest record will override
the value and hence we get unique ???

1 - 10 keys......

But when we use CURSOR the results should be like

1,
1,1
1,2....so on ...

The results you showed is executed on DB2???

Well my interpretation of Query may be wrong , DavidatK can you please throw some light on the Query execution......I will be glad to know.

And oops i forgot ....Welcome Dave to IBMMAINFRAMEGURU.

Thanks,
Veera