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: 10274
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 Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts scheduling between OPC and TWS give u... nsbl IBM Tools 4 Sun Mar 12, 2017 5:35 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am


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