Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 10396
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 reg query on DYNALLOC feature raghuraman123 SYNCSORT 12 Wed Jan 10, 2018 2:42 pm
No new posts query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 4 Tue Nov 07, 2017 8:34 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us