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
 

 

Need Query for the following result

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
itdsen

New User


Joined: 20 Sep 2006
Posts: 23
Location: Chennai

PostPosted: Wed Nov 05, 2008 2:30 pm    Post subject: Need Query for the following result
Reply with quote

Hi,

there are two tables table1 & table2

Table 1
no name address
1 xxxx ..........
1 zzzzz mmmm
2 sssss vvvvvv
4 aaaa qqqqqq
..............
......... so on

Table 2
no price s.no
1 100 1
1 200 2
1 000 3
1 210 4
2 000 1
2 110 2
3 100 1
4 160 3
4 100 1
...... so on


i need result as like

no name price price price price
when s.no=1 s.no=2 s.no=3 s.no=4

1 xxxx 100 200 000 210
1 zzzz 100 200 000 210
2 ssss 000 110 000 000
4 aaaa 100 000 160 000
...... so on
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Nov 05, 2008 5:56 pm    Post subject:
Reply with quote

This is not possible ...you cant mix and match different rows ...why dont you try this out in your program using stringing functions ?
Back to top
View user's profile Send private message
Amsar

New User


Joined: 26 Sep 2008
Posts: 84
Location: Chennai

PostPosted: Wed Nov 05, 2008 6:46 pm    Post subject:
Reply with quote

Quote:
This is not possible

I think it is icon_biggrin.gif

I just tried with some sample and it's working for me.
Code:
TAB1
----
NO     
NAME   
ADDRESS

TAB2
----
NO   
PRICE
SRNO


Code:
  SELECT NO,NAME,                                             
         MAX(CASE WHEN  SRNO = 1 THEN PRICE ELSE 0  END),
         MAX(CASE WHEN  SRNO = 2 THEN PRICE ELSE 0  END),
         MAX(CASE WHEN  SRNO = 3 THEN PRICE ELSE 0  END),
         MAX(CASE WHEN  SRNO = 4 THEN PRICE ELSE 0  END)
  FROM                                                       
  (                                                           
  SELECT A.NO, NAME, PRICE, SRNO FROM                         
  TAB1 A,                                                     
  TAB2 B                                                     
  WHERE A.NO = B.NO                                           
  ) T1                                                       
  GROUP BY NO,NAME;

OUTPUT
Code:
---------+---------+---------+---------+---------+---------+-----
         NO  NAME                                               
---------+---------+---------+---------+---------+---------+-----
          1  xxxx         100         200         000         210
          1  zzzzz        100         200         000         210
          2  sssss        000         110         000         000
          4  aaaa         100         000         160         000
DSNE610I NUMBER OF ROWS DISPLAYED IS 4                           
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100     
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10211
Location: italy

PostPosted: Wed Nov 05, 2008 6:50 pm    Post subject: Reply to: Need Query for the following result
Reply with quote

it works because You set a limit on the number of report columns to be filled!

for the more general case of unknown number of report columns you will have to write a program
Back to top
View user's profile Send private message
Amsar

New User


Joined: 26 Sep 2008
Posts: 84
Location: Chennai

PostPosted: Wed Nov 05, 2008 6:58 pm    Post subject: Re: Need Query for the following result
Reply with quote

itdsen wrote:
Hi,

i need result as like

no name price price price price
when s.no=1 s.no=2 s.no=3 s.no=4

1 xxxx 100 200 000 210
1 zzzz 100 200 000 210
2 ssss 000 110 000 000
4 aaaa 100 000 160 000
...... so on


enrico,

Yes it is limited to 4. But the expected results given by the OP does n't say anything about an extra column like when s.no=5 or greater. I guess now it's upto him to comeback on this.
Back to top
View user's profile Send private message
itdsen

New User


Joined: 20 Sep 2006
Posts: 23
Location: Chennai

PostPosted: Fri Nov 07, 2008 1:04 pm    Post subject: Reply to: Need Query for the following result
Reply with quote

Thanks Amsar :D :D

Query is working fine.
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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