get record number added to query 3

[kkitt]

I am looking for a way to add a record number count to a selection query like below:

select field1, field2, field3
  from table
 where field1 = 'data'

output is:

  field1        field2     field3
  ------        ------     ------
   data          data       data
   ...           ...         ...

what I am looking for is:

select recnum?, field1, field2, field3
  from table
 where field1 = 'data'

output to be:

   recnum?      field1    field2    field2
   -------      ------    ------    ------
      1.         data      data     data
      2.          ...      ....      ...
etc....

Thanks in advance

 

[blom0344]

Consider OLAP function Row_Number():

SELECT ROWNUMBER() OVER(ORDER BY FIELD1), FIELD1,FIELD2,FIELD3 FROM TABLE

Ties Blom
Information analyst

 

[juliane26]

you just need a counter or what is the purpose?

Be aware, that when re-running the query the rownumber() will not be the same when data physically changed.

And the function is not available on z/OS.

Juliane

 

[kkitt]

This is for DB2 V8 running under z/OS. I am just looking to add a counter to the query.

thanks in advance

 

[MarcLodge]

The standard way for doing this on z/os is:

SELECT DEPTNO, ROW# FROM DSN8810.DEPT TB1,
TABLE (SELECT COUNT(*) AS ROW#
FROM DSN8810.DEPT TB2
WHERE TB2.DEPTNO < TB1.DEPTNO) AS TEMP_TAB;

For more detailed and varied examples see here:

http://www-1.ibm.com/support/docvie...00&uid=swg27005359&loc=en_US&cs=UTF-8&lang=en

Marc

 

[juliane26]

Marc,
I think you earned a star.

Juliane

 

[kkitt]

I am getting close what I have coded is:

SELECT PEIM_ID, ROW#                      
  FROM T819PEIM TB1,                      
 TABLE ( SELECT COUNT(*) AS ROW#          
           FROM T819PEIM TB2              
          WHERE TB2.PEIM_ID < TB1.PEIM_ID 
        ) AS TEMP_TAB                     
 WHERE PEIM_YEAR = '2005'                 
   AND PEIM_ID  LIKE '46581%%%%'          

---------+---------+---
PEIM_ID           ROW# 
---------+---------+---
465810276       217810 
465810301       217811 
465810405       217812 
465810425       217813 
465810735       217814 
465810875       217815 
465810886       217816 
465810910       217817 
465810911       217818 
465810921       217819 
465811193       217820

but what I am looking for is

---------+---------+---
PEIM_ID           ROW# 
---------+---------+---
465810276          1
465810301          2 
465810405          3
465810425          4
465810735          5
465810875          6
465810886          7
465810910          8
465810911          9
465810921          10
465811193          11

Any thoughts

Thanks in advance.

 

[MarcLodge]

kkitt,
Try adding the outer where clauses into the inner table:
SELECT PEIM_ID, ROW#
FROM T819PEIM TB1,
TABLE ( SELECT COUNT(*) AS ROW#
FROM T819PEIM TB2
WHERE TB2.PEIM_ID < TB1.PEIM_ID
AND TB2.PEIM_YEAR = '2005'
AND TB2.PEIM_ID LIKE '46581%%%%'
) AS TEMP_TAB
WHERE PEIM_YEAR = '2005'
AND PEIM_ID LIKE '46581%%%%'

I think that might make the difference you are looking for.

Juliane, many thanks for the star

 

[kkitt]

thanks, this works great....

I just needed to change the 'count(*)' to 'count(*) + 1' to get it starting from 1 rather then zero.

I have included a sample of one of the queries that I needed to add it to. Get messy when joining tables and such, LOL

SELECT HIST_PEIMS, ROW#                               
  FROM ( SELECT HSTU_PEIMS    AS HIST_PEIMS,          
                'H'           AS HIST_TYPE            
           FROM T819HSTU                              
          WHERE HSTU_PEIMS LIKE '46581%%%%'           
         UNION ALL                                    
         SELECT HSST_PEIMS      AS HIST_PEIMS,        
                'S'             AS HIST_TYPE          
           FROM T819HSST                              
          WHERE HSST_PEIMS LIKE '46581%%%%'           
       ) AS A,                                        
   TABLE(SELECT COUNT(*) + 1 AS ROW#                  
           FROM ( SELECT HSTU_PEIMS AS HIST_PEIMS
                    FROM T819HSTU                     
                   WHERE HSTU_PEIMS LIKE '46581%%%%'  
                  UNION ALL                           
                  SELECT HSST_PEIMS AS HIST_PEIMS
                    FROM T819HSST                       
                   WHERE HSST_PEIMS LIKE '46581%%%%'    
                )AS B                                   
           WHERE B.HIST_PEIMS < A.HIST_PEIMS) AS TEM_TAB
 WHERE HIST_TYPE IN ('H',' ')                           
 ORDER BY 2 DESC

and this is one of the simpler ones, OH WOE is ME.......

Another Star for the help