Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

How can I get the odd and even number of rows from a DB2 table?

Can I get the odd and even number of rows from a DB2 table? If this is possible, do you have access to the query with examples? Thank you.

Well, first of all, let me correct what is perhaps a misunderstanding about DB2 tables. The data in the rows is not ordered. So there is no way to guarantee that the rows are odd or even numbered.

With that aside, let me try to come up with a "solution" to your problem. The first thing you have to do is to mimic row numbers in DB2. DB2 does not support the row number construct (such as you can find in Oracle). To do this, use the COUNT(*) function and a table expression. A table expression is when you substitute SQL in place of the table in the FROM clause of another SQL statement. For example, consider this SQL:

SELECT  DEPTNO, ROWNUM
FROM DSN8810.DEPT A,
TABLE (SELECT COUNT(*) + 1 AS ROWNUM
FROM DSN8810.DEPT B
WHERE B.DEPTNO < A.DEPTNO) AS TEMP_TAB;

That puts a pseudo-row number on the table. Now, say you only want the even results. This can be done using DB2 functions.

SELECT  DEPTNO, ROWNUM
FROM DSN8810.DEPT A,
TABLE (SELECT COUNT(*) + 1 AS ROWNUM
FROM DSN8810.DEPT B
WHERE B.DEPTNO < A.DEPTNO) AS TEMP_TAB
WHERE MOD(ROWNUM,2) = 0
ORDER BY ROWNUM;

If you want the odd rows only, change the predicate with the MOD function to this:

WHERE MOD(ROWNUM,2) <> 0

Dig Deeper on DB2 UDB (universal databases)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close