SQL Help-Retrieving only fixed number of rows in S/390

syed asif

SQL Help-Retrieving only fixed number of rows in S/390
Hi Group,

I wanted to know if anyone in the group has used SQL
on S/390 (DB2 V6) to select only first 10 of the
table. Like we have in DB2 UDB "SELECT * FROM
COOLADM.ALLOW_CAT_ALC FETCH FIRST 10 ROW ONLY"

I need to select only 10 records from a table. Any
help will be appriciated.Looking forward to the
response.
Regards,
Syed

=====
syed asif fazal
954-447-5568(W)
954-322-5414(H)

__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com



Dave Nance

Re: SQL Help-Retrieving only fixed number of rows in S/390
(in response to syed asif)
Syed,
This is a version 7 feature. To do this in V6 or below, you have to do it programatically. Open a cursor, fetch record, increment a counter, when counter = 10, close cursor.

>>> [login to unmask email] 12/11/01 10:50AM >>>
Hi Group,

I wanted to know if anyone in the group has used SQL
on S/390 (DB2 V6) to select only first 10 of the
table. Like we have in DB2 UDB "SELECT * FROM
COOLADM.ALLOW_CAT_ALC FETCH FIRST 10 ROW ONLY"

I need to select only 10 records from a table. Any
help will be appriciated.Looking forward to the
response.
Regards,
Syed

=====
syed asif fazal
954-447-5568(W)
954-322-5414(H)

__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com




This message, including any attachments, is intended solely for the use of the named
recipient(s) and may contain confidential and/or privileged information. Any
unauthorized review, use, disclosure or distribution of this communications is expressly
prohibited. If you are not the intended recipient, please contact the sender by reply e-mail
and destroy any and all copies of the original message. Thank you.



Raymond Bell

Re: SQL Help-Retrieving only fixed number of rows in S/390
(in response to Dave Nance)
Oh man, how much am I NOT going to answer this one... ;o)


Raymond



syed asif
<syed_asif_98@ To: [login to unmask email]
YAHOO.COM> cc:
Sent by: DB2 Subject: SQL Help-Retrieving only fixed number of rows in S/390
Data Base
Discussion
List
<[login to unmask email]
M>


12/12/01 04:50
Please respond
to DB2 Data
Base
Discussion
List





Hi Group,

I wanted to know if anyone in the group has used SQL
on S/390 (DB2 V6) to select only first 10 of the
table. Like we have in DB2 UDB "SELECT * FROM
COOLADM.ALLOW_CAT_ALC FETCH FIRST 10 ROW ONLY"

I need to select only 10 records from a table. Any
help will be appriciated.Looking forward to the
response.
Regards,
Syed

=====
syed asif fazal
954-447-5568(W)
954-322-5414(H)

__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com








Ray Gaston

Re: SQL Help-Retrieving only fixed number of rows in S/390
(in response to Raymond Bell)
Syed...The solution is in the archives. This has been reviewed often.

-Ray

> -----Original Message-----
> From: Raymond Bell [SMTP:[login to unmask email]
> Sent: Tuesday, December 11, 2001 3:29 PM
> To: [login to unmask email]
> Subject: Re: SQL Help-Retrieving only fixed number of rows in S/390
>
> Oh man, how much am I NOT going to answer this one... ;o)
>
>
> Raymond
>
>
>
> syed asif
> <syed_asif_98@ To: [login to unmask email]
> YAHOO.COM> cc:
> Sent by: DB2 Subject: SQL Help-Retrieving
> only fixed number of rows in S/390
> Data Base
> Discussion
> List
> <[login to unmask email]
> M>
>
>
> 12/12/01 04:50
> Please respond
> to DB2 Data
> Base
> Discussion
> List
>
>
>
>
>
> Hi Group,
>
> I wanted to know if anyone in the group has used SQL
> on S/390 (DB2 V6) to select only first 10 of the
> table. Like we have in DB2 UDB "SELECT * FROM
> COOLADM.ALLOW_CAT_ALC FETCH FIRST 10 ROW ONLY"
>
> I need to select only 10 records from a table. Any
> help will be appriciated.Looking forward to the
> response.
> Regards,
> Syed
>
> =====
> syed asif fazal
> 954-447-5568(W)
> 954-322-5414(H)
>
> __________________________________________________
> Do You Yahoo!?
> Check out Yahoo! Shopping and Yahoo! Auctions for all of
> your unique holiday gifts! Buy at http://shopping.yahoo.com
> or bid at http://auctions.yahoo.com
>
>
>
>
>
>
>
>
>
>



Troy Coleman

Re: SQL Help-Retrieving only fixed number of rows in S/390
(in response to Ray Gaston)
DB2 V7

EXEC SQL DECLARE C1 CURSOR FOR
SELECT * FROM X.RMT_TAB
FETCH FIRST 50 ROWS Only;

Time to upgrade :)

Troy Coleman
Coleman Consulting, Inc.

>--- Original Message ---
>From: Raymond Bell <[login to unmask email]>
>To: [login to unmask email]
>Date: 12/11/01 2:29:06 PM
>

>Oh man, how much am I NOT going to answer this one... ;o)
>
>
>Raymond
>
>
>
> syed asif
> <syed_asif_98@ to: [login to unmask email]
yahoo.com> cc:
> Sent by: DB2 Subject: SQL Help-Retrieving
only fixed number of rows in S/390
> Data Base
> Discussion
> List
> <[login to unmask email]
m>
>
>
> 12/12/01 04:50
> Please respond
> to DB2 Data
> Base
> Discussion
> List
>
>
>
>
>
>Hi Group,
>
> I wanted to know if anyone in the group has used SQL
>on S/390 (DB2 V6) to select only first 10 of the
>table. Like we have in DB2 UDB "SELECT * FROM
>COOLADM.ALLOW_CAT_ALC FETCH FIRST 10 ROW ONLY"
>
>I need to select only 10 records from a table. Any
>help will be appriciated.Looking forward to the
>response.
>Regards,
>Syed
>
>=====
>syed asif fazal
> 954-447-5568(W)
> 954-322-5414(H)
>
>__________________________________________________
>Do You Yahoo!?
>Check out Yahoo! Shopping and Yahoo! Auctions for all of
>your unique holiday gifts! Buy at http://shopping.yahoo.com
>or bid at http://auctions.yahoo.com
>
>
>
visit
>the DB2-L webpage at http://www.ryci.com/db2-l. The owners of
the list can
>
>
>
>
visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners
of the list can
>



Larry Kintisch

Re: SQL Help-Retrieving only fixed number of rows in S/390
(in response to Troy Coleman)
Hi Syed,
If you wait until DB2 OS/390 v7 you'll have the FETCH FIRST syntax that's
not yet in V6 but is in Win/Unix.
If you are selecting by a primary key value, say ORDERNO, you could use
the following:
SELECT * FROM T1 WHERE ORDERNO <=
(SELECT MIN(ORDERNO) FROM T1 WHERE ORDERNO >
(SELECT MIN(ORDERNO) FROM T1 WHERE ORDERNO >
(SELECT MIN(ORDERNO) FROM T1 WHERE ORDERNO >
etc. totaling 10 subselects ending with 10 ))).
If it is a large table, do an EXPLAIN first to make sure you are not doing
10 table scans.
If you are not comparing by keys, but just want to know the first 10
records as stored at the beginning of the table, there is no simple way
using SQL. QMF has "ROW number" as a "usage" code and you could play with
that. Or you could do a Select * with no ORDER BY in SPUFI, but set the
SPUFI default output row count to 10. With those 10 rows you can do a
select ..WHERE ORDERNO IN(...) keying into the IN the 10 key values that
show up. Hope this helps. Larry Kintisch

At 07:50 AM 12/11/01 -0800, you wrote:
>Hi Group,
>
> I wanted to know if anyone in the group has used SQL
>on S/390 (DB2 V6) to select only first 10 of the
>table. Like we have in DB2 UDB "SELECT * FROM
>COOLADM.ALLOW_CAT_ALC FETCH FIRST 10 ROW ONLY"
>
>I need to select only 10 records from a table. Any
>help will be appriciated.Looking forward to the
>response.
>Regards,
>Syed
>
>=====
>syed asif fazal
> 954-447-5568(W)
> 954-322-5414(H)
>
>__________________________________________________
>Do You Yahoo!?
>Check out Yahoo! Shopping and Yahoo! Auctions for all of
>your unique holiday gifts! Buy at http://shopping.yahoo.com
>or bid at http://auctions.yahoo.com
>
>
>


>

Larry Kintisch, Pres. e-mail: [login to unmask email]
Able Information Services phone: (845)-353-3809
"DB2, QMF and Data Modeling"
208 Hilltop Drive PO Box 809
Nyack NY 10960-0809



Mohammed Nayeem

SQL help
(in response to Larry Kintisch)
Hi DB2-List members

Under DB2 V6.1 OS/390 environment

Following table TABA

+-------------------------+
| Col1 | col2 | col3 |
+-------------------------+
| 1 | AAA | A |
| 2 | AAA | A |
| 3 | BBB | B |
| 4 | AAA | B |
| 6 | AAA | A |
| 7 | AAA | A |
| 8 | CCC | C |
| 9 | AAA | B |
| 10 | AAA | A |
| 12 | AAA | A |
| 13 | BBB | B |
| 14 | AAA | B |
| 15 | AAC | C |
| 16 | AAA | A |
| 17 | BBB | B |
| 18 | AAC | B |
| 19 | BBB | C |
| 20 | AAC | B |
| 22 | AAC | B |
| 23 | AAA | A |
| 24 | AAA | A |
| 25 | AAA | B |
| 26 | AAA | A |
| 23 | AAC | B |

millions of rows exist in the table.

and my requirment is to find distinct rows
(col2 & col3)

Example :

OUTPUT SHOULD RETURN DISTINCT combination
+-----------------+
|col2 | col3 |
+-----------------+
| AAA | A |
| AAA | B |
| AAA | C |
| BBB | B |
| BBB | C |
| AAC | B |
| AAC | C |


I am trying this way :

SELECT X.COL2 , X.COL3
FROM TABA X
WHERE COL2 IN
( SELECT DISTINCT Y.COL2
FROM TABA Y
WHERE X.COL2 = Y.COL2
GROUP BY X.COL2,X.COL3
)
ORDER BY X.COL2,X.COL3


But i am not getting what I need.

Any help on this is appreciated.


Thanks in advance
DB2 DBA






__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com



Francis C - CNF Leblanc

Re: SQL help
(in response to Mohammed Nayeem)
Unless I misunderstood your requirement the following SQL statement should
work.

SELECT DISTINCT col2, col3
FROM TABA;

Obviously this will perform best if there is an index on col2 and col3.

> -----Original Message-----
> From: Database Admin [SMTP:[login to unmask email]
> Sent: Thursday, December 20, 2001 9:13 AM
> To: [login to unmask email]
> Subject: SQL help
>
> Hi DB2-List members
>
> Under DB2 V6.1 OS/390 environment
>
> Following table TABA
>
> +-------------------------+
> | Col1 | col2 | col3 |
> +-------------------------+
> | 1 | AAA | A |
> | 2 | AAA | A |
> | 3 | BBB | B |
> | 4 | AAA | B |
> | 6 | AAA | A |
> | 7 | AAA | A |
> | 8 | CCC | C |
> | 9 | AAA | B |
> | 10 | AAA | A |
> | 12 | AAA | A |
> | 13 | BBB | B |
> | 14 | AAA | B |
> | 15 | AAC | C |
> | 16 | AAA | A |
> | 17 | BBB | B |
> | 18 | AAC | B |
> | 19 | BBB | C |
> | 20 | AAC | B |
> | 22 | AAC | B |
> | 23 | AAA | A |
> | 24 | AAA | A |
> | 25 | AAA | B |
> | 26 | AAA | A |
> | 23 | AAC | B |
>
> millions of rows exist in the table.
>
> and my requirment is to find distinct rows
> (col2 & col3)
>
> Example :
>
> OUTPUT SHOULD RETURN DISTINCT combination
> +-----------------+
> |col2 | col3 |
> +-----------------+
> | AAA | A |
> | AAA | B |
> | AAA | C |
> | BBB | B |
> | BBB | C |
> | AAC | B |
> | AAC | C |
>
>
> I am trying this way :
>
> SELECT X.COL2 , X.COL3
> FROM TABA X
> WHERE COL2 IN
> ( SELECT DISTINCT Y.COL2
> FROM TABA Y
> WHERE X.COL2 = Y.COL2
> GROUP BY X.COL2,X.COL3
> )
> ORDER BY X.COL2,X.COL3
>
>
> But i am not getting what I need.
>
> Any help on this is appreciated.
>
>
> Thanks in advance
> DB2 DBA
>
>
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Check out Yahoo! Shopping and Yahoo! Auctions for all of
> your unique holiday gifts! Buy at http://shopping.yahoo.com
> or bid at http://auctions.yahoo.com
>
>
>
>
>



Ben Relle

Re: SQL help
(in response to Francis C - CNF Leblanc)
I think I've missed something(in fact I'm sure I have), but

Select distinct col2,col3 from taba

should do it.

Database Admin wrote:

> Hi DB2-List members
>
> Under DB2 V6.1 OS/390 environment
>
> Following table TABA
>
> +-------------------------+
> | Col1 | col2 | col3 |
> +-------------------------+
> | 1 | AAA | A |
> | 2 | AAA | A |
> | 3 | BBB | B |
> | 4 | AAA | B |
> | 6 | AAA | A |
> | 7 | AAA | A |
> | 8 | CCC | C |
> | 9 | AAA | B |
> | 10 | AAA | A |
> | 12 | AAA | A |
> | 13 | BBB | B |
> | 14 | AAA | B |
> | 15 | AAC | C |
> | 16 | AAA | A |
> | 17 | BBB | B |
> | 18 | AAC | B |
> | 19 | BBB | C |
> | 20 | AAC | B |
> | 22 | AAC | B |
> | 23 | AAA | A |
> | 24 | AAA | A |
> | 25 | AAA | B |
> | 26 | AAA | A |
> | 23 | AAC | B |
>
> millions of rows exist in the table.
>
> and my requirment is to find distinct rows
> (col2 & col3)
>
> Example :
>
> OUTPUT SHOULD RETURN DISTINCT combination
> +-----------------+
> |col2 | col3 |
> +-----------------+
> | AAA | A |
> | AAA | B |
> | AAA | C |
> | BBB | B |
> | BBB | C |
> | AAC | B |
> | AAC | C |
>
> I am trying this way :
>
> SELECT X.COL2 , X.COL3
> FROM TABA X
> WHERE COL2 IN
> ( SELECT DISTINCT Y.COL2
> FROM TABA Y
> WHERE X.COL2 = Y.COL2
> GROUP BY X.COL2,X.COL3
> )
> ORDER BY X.COL2,X.COL3
>
> But i am not getting what I need.
>
> Any help on this is appreciated.
>
> Thanks in advance
> DB2 DBA
>
> __________________________________________________
> Do You Yahoo!?
> Check out Yahoo! Shopping and Yahoo! Auctions for all of
> your unique holiday gifts! Buy at http://shopping.yahoo.com
> or bid at http://auctions.yahoo.com
>
>
>



Isaac Yassin

Re: SQL help
(in response to Ben Relle)
Hi

select col2,col3
from table
group by col2,col3

there's an example in the SQL guide.

Isaac Yassin
[login to unmask email]
----- Original Message -----
From: "Database Admin" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, December 20, 2001 7:12 PM
Subject: SQL help


> Hi DB2-List members
>
> Under DB2 V6.1 OS/390 environment
>
> Following table TABA
>
> +-------------------------+
> | Col1 | col2 | col3 |
> +-------------------------+
> | 1 | AAA | A |
> | 2 | AAA | A |
> | 3 | BBB | B |
> | 4 | AAA | B |
> | 6 | AAA | A |
> | 7 | AAA | A |
> | 8 | CCC | C |
> | 9 | AAA | B |
> | 10 | AAA | A |
> | 12 | AAA | A |
> | 13 | BBB | B |
> | 14 | AAA | B |
> | 15 | AAC | C |
> | 16 | AAA | A |
> | 17 | BBB | B |
> | 18 | AAC | B |
> | 19 | BBB | C |
> | 20 | AAC | B |
> | 22 | AAC | B |
> | 23 | AAA | A |
> | 24 | AAA | A |
> | 25 | AAA | B |
> | 26 | AAA | A |
> | 23 | AAC | B |
>
> millions of rows exist in the table.
>
> and my requirment is to find distinct rows
> (col2 & col3)
>
> Example :
>
> OUTPUT SHOULD RETURN DISTINCT combination
> +-----------------+
> |col2 | col3 |
> +-----------------+
> | AAA | A |
> | AAA | B |
> | AAA | C |
> | BBB | B |
> | BBB | C |
> | AAC | B |
> | AAC | C |
>
>
> I am trying this way :
>
> SELECT X.COL2 , X.COL3
> FROM TABA X
> WHERE COL2 IN
> ( SELECT DISTINCT Y.COL2
> FROM TABA Y
> WHERE X.COL2 = Y.COL2
> GROUP BY X.COL2,X.COL3
> )
> ORDER BY X.COL2,X.COL3
>
>
> But i am not getting what I need.
>
> Any help on this is appreciated.
>
>
> Thanks in advance
> DB2 DBA
>
>
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Check out Yahoo! Shopping and Yahoo! Auctions for all of
> your unique holiday gifts! Buy at http://shopping.yahoo.com
> or bid at http://auctions.yahoo.com
>
>
> DB2-L webpage at http://www.ryci.com/db2-l. The
owners of the list can
>



Sam Baugh

Re: SQL help
(in response to Bayard Tysor)
Try:
SELECT DISTINCT COL2, COL3
FROM TABA

-----Original Message-----
From: Database Admin [mailto:[login to unmask email]
Sent: Thursday, December 20, 2001 11:13 AM
To: [login to unmask email]
Subject: SQL help


Hi DB2-List members

Under DB2 V6.1 OS/390 environment

Following table TABA

+-------------------------+
| Col1 | col2 | col3 |
+-------------------------+
| 1 | AAA | A |
| 2 | AAA | A |
| 3 | BBB | B |
| 4 | AAA | B |
| 6 | AAA | A |
| 7 | AAA | A |
| 8 | CCC | C |
| 9 | AAA | B |
| 10 | AAA | A |
| 12 | AAA | A |
| 13 | BBB | B |
| 14 | AAA | B |
| 15 | AAC | C |
| 16 | AAA | A |
| 17 | BBB | B |
| 18 | AAC | B |
| 19 | BBB | C |
| 20 | AAC | B |
| 22 | AAC | B |
| 23 | AAA | A |
| 24 | AAA | A |
| 25 | AAA | B |
| 26 | AAA | A |
| 23 | AAC | B |

millions of rows exist in the table.

and my requirment is to find distinct rows
(col2 & col3)

Example :

OUTPUT SHOULD RETURN DISTINCT combination
+-----------------+
|col2 | col3 |
+-----------------+
| AAA | A |
| AAA | B |
| AAA | C |
| BBB | B |
| BBB | C |
| AAC | B |
| AAC | C |


I am trying this way :

SELECT X.COL2 , X.COL3
FROM TABA X
WHERE COL2 IN
( SELECT DISTINCT Y.COL2
FROM TABA Y
WHERE X.COL2 = Y.COL2
GROUP BY X.COL2,X.COL3
)
ORDER BY X.COL2,X.COL3


But i am not getting what I need.

Any help on this is appreciated.


Thanks in advance
DB2 DBA






__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com








Bayard Tysor

Re: SQL help
(in response to Isaac Yassin)
Try

> SELECT DISTINCT X.COL2 , X.COL3
> FROM TABA X
> ORDER BY X.COL2,X.COL3

Tink
--- Database Admin <[login to unmask email]> wrote:
> Hi DB2-List members
>
> Under DB2 V6.1 OS/390 environment
>
> Following table TABA
>
> +-------------------------+
> | Col1 | col2 | col3 |
> +-------------------------+
> | 1 | AAA | A |
> | 2 | AAA | A |
> | 3 | BBB | B |
> | 4 | AAA | B |
> | 6 | AAA | A |
> | 7 | AAA | A |
> | 8 | CCC | C |
> | 9 | AAA | B |
> | 10 | AAA | A |
> | 12 | AAA | A |
> | 13 | BBB | B |
> | 14 | AAA | B |
> | 15 | AAC | C |
> | 16 | AAA | A |
> | 17 | BBB | B |
> | 18 | AAC | B |
> | 19 | BBB | C |
> | 20 | AAC | B |
> | 22 | AAC | B |
> | 23 | AAA | A |
> | 24 | AAA | A |
> | 25 | AAA | B |
> | 26 | AAA | A |
> | 23 | AAC | B |
>
> millions of rows exist in the table.
>
> and my requirment is to find distinct rows
> (col2 & col3)
>
> Example :
>
> OUTPUT SHOULD RETURN DISTINCT combination
> +-----------------+
> |col2 | col3 |
> +-----------------+
> | AAA | A |
> | AAA | B |
> | AAA | C |
> | BBB | B |
> | BBB | C |
> | AAC | B |
> | AAC | C |
>
>
> I am trying this way :
>
> SELECT X.COL2 , X.COL3
> FROM TABA X
> WHERE COL2 IN
> ( SELECT DISTINCT Y.COL2
> FROM TABA Y
> WHERE X.COL2 = Y.COL2
> GROUP BY X.COL2,X.COL3
> )
> ORDER BY X.COL2,X.COL3
>
>
> But i am not getting what I need.
>
> Any help on this is appreciated.
>
>
> Thanks in advance
> DB2 DBA
>
>
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Check out Yahoo! Shopping and Yahoo! Auctions for
> all of
> your unique holiday gifts! Buy at
> http://shopping.yahoo.com
> or bid at http://auctions.yahoo.com
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can


=====
B.L. "Tink" Tysor
Bayard Lee Tysor, Inc
(401)965-2688
www.BLTysor.com

__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com