Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

A query to display same row multiple times with count

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: A query to display same row multiple times with count
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    Post subject: Re: A query to display same row multiple times with count
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    Post subject:
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    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Fri Feb 23, 2007 9:05 am    Post subject:
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    Post subject: Re: A query to display same row multiple times with count
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

Site Director


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

PostPosted: Fri Feb 23, 2007 9:47 am    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Fri Feb 23, 2007 8:23 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Sat Feb 24, 2007 7:20 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
This topic is locked: you cannot edit posts or make replies. SDSF multiple spool datasets extracte... PJAlarcon CLIST & REXX 1 Fri Apr 21, 2017 10:50 pm
No new posts Validating file with multiple header/... mohitsaini DFSORT/ICETOOL 6 Thu Apr 13, 2017 1:53 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us