IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

A query to display same row multiple times with count


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Sat Feb 17, 2007 5:04 am
Reply with quote

Hi,

Suppose table T is as follows.

col1 col2
----- -----
A 2
B 3


How to write a query that displays

A 1
A 2
B 1
B 2
B 3

Thanks!
Back to top
View user's profile Send private message
nileshp

New User


Joined: 25 Feb 2006
Posts: 31

PostPosted: Wed Feb 21, 2007 3:41 pm
Reply with quote

Hi,
Can you explain your requirement in detail? From your post I am not able to understand want you want?
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Thu Feb 22, 2007 10:59 am
Reply with quote

Suppose table T is as follows.

Code:
col1 col2
----- -----
A      2
B      3



Since row1 has value 2 in col2 for A in col1, A is to be displayed twice. Similarly, B to be displayed thrice. And if possible, have an incrementing count also displayed to the right as shown below.

Code:
A     1
A     2
B     1
B     2
B     3


Hope it helps!
Back to top
View user's profile Send private message
speedcnu

New User


Joined: 27 Jan 2004
Posts: 6
Location: Hyderabad

PostPosted: Thu Feb 22, 2007 12:38 pm
Reply with quote

Select * from table T order by col1,col2
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Fri Feb 23, 2007 6:38 am
Reply with quote

Order by just sorts the rows selected for display. It doesn't generate rows or change the display values.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Feb 23, 2007 9:05 am
Reply with quote

Hello,

If you're willing to do 2 "passes" you could read all of the rows and as you read each one generate the necessary "new" rows in a temporary table. You could then read the temporary table and report the rows that you built to your specification.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Fri Feb 23, 2007 9:40 am
Reply with quote

Hi Dick,

I agree that more than one 'pass' will be needed. But how do I generate more than one row using SQL? In my example above, 'A' has to be displayed twice and 'B' to be displayed thrice.

Thanks!
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Feb 23, 2007 9:47 am
Reply with quote

Hi W,

I was focusing on the results rather than the method.

For mechanics, you could declare a cursor that was "SELECT c1, c2, etc FROM tbl1, FETCH all of the rows via the cursor, and as each row is FETCHed, insert the required incremental rows in the temporary table.

Once you exhaust all of the rows with the FETCH, close the cursor and do whatever you need to with the rows in the temporary table.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Fri Feb 23, 2007 10:03 am
Reply with quote

Dick,

If I decide to use cursor and a cobol program then it is easy where the row genaration part is essentially handled by the program. But I am interested in completing the operation using SQL only.

thanks!
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Feb 23, 2007 8:23 pm
Reply with quote

Hi W,

Well, maybe someone will have an "SQL only" suggestion. So far, in a week, nobody has yet jumped in with one icon_wink.gif A bit of "good" news is that if you were to look in some of the SqlServer or Oracle forums, they sling a bunch of SQL code and there may be something in one of those.

As i mentioned, my approach to something is to make sure i understand what the business need is and then i figure which approach to use. I try to avoid predetermining "how" a thing is to be done and focus on making sure that what is done completely fills the requirement (and is maintainable as well as not horribly wasting machine resources (like a cartesian product).

One other thought (and i personally apply it to lots of opportunities) is that while i may be able to figure out a "trick" with SQL or the sort or some other utility, what happens when the solution has been running for a while and there is an additional requirement added - one that cannot be met with the "trick" solution? Then, what could have been a trivial change, becomes a (re-)write rather than a simple enhancement.

Good luck and if i come across anything that looks like what you want, i'll post it icon_smile.gif
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Sat Feb 24, 2007 6:57 am
Reply with quote

Dick,

I understand your concerns about implementing such a solution as a short term fix.

Actually I no longer require it as part of my testing so it has become sort of a curiosity to check if it can be done.

And I found a solution from another forum. It involved a little bit of extra work but it was a SQL-only solution. Not sure if the forum rules forbid linking to another forum. So just quoting him here.

Quote:
if you don't already have one, start by creating an integers table

Code:

create table integers
(i integer not null primary key);
insert into integers (i) values (0);
insert into integers (i) values (1);
insert into integers (i) values (2);
insert into integers (i) values (3);
insert into integers (i) values (4);
insert into integers (i) values (5);
insert into integers (i) values (6);
insert into integers (i) values (7);
insert into integers (i) values (8);
insert into integers (i) values (9);


select yourtable.col1
, integers i
from yourtable
inner
join integers
on integers.i between 1 and yourtable.col2
order
by yourtable.col1
, integers i


This inner join with a between clause is fantastic! I never knew one could use a range of values like that in JOIN.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sat Feb 24, 2007 7:20 am
Reply with quote

Good find icon_biggrin.gif

Thanx for posting what you found. I'll wager that someone else will have a similar challange and this may save them some time.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts RC query -Time column CA Products 3
Search our Forums:

Back to Top