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
 

 

Display function result in a perticular row

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

New User


Joined: 16 Oct 2006
Posts: 80
Location: chennai

PostPosted: Mon Jul 26, 2010 6:16 pm    Post subject: Display function result in a perticular row
Reply with quote

Hi,

I need to display the one executed query function result in another row. I have attached the requirement which is in EXCEL format. I am using SPUFI option in FILE-AID for the perticular table. Please help me to generate a query.

Thanks and Regards,
Nath.
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Mon Jul 26, 2010 6:42 pm    Post subject:
Reply with quote

Please DO NOT post attachments as not every one is able to open them. This reduces the number of people available to help you.
Back to top
View user's profile Send private message
rguhanath

New User


Joined: 16 Oct 2006
Posts: 80
Location: chennai

PostPosted: Mon Jul 26, 2010 7:13 pm    Post subject:
Reply with quote

Hi,

Sorry for using attachment. Below i have given the requirement and details.

Code:

TABLE                
COL1   COL2   COL3   COST   ID
-----------------------------------------------------------------------------
ABC    DEF    GHI    0       1
ABC    DEF    GHI    2.5     2
ABC    DEF    GHI    3.5     2   
PQR    STU    VWX    0       1   
PQR    STU    VWX    4.5     2   
PQR    STU    VWX    5.5     2   
----------------------------------------------------------------------------

REQUIRED RESULT FROM ABOVE TABLE               
               
COL1   COL2   COL3   SUM(COST)   ID   
-----------------------------------------------------------------------------
ABC    DEF    GHI     6.00        1   
PQR    STU    VWX    10.00        1   
-----------------------------------------------------------------------------

Need the out put which are having the 'ID=1' with the summation of cost.
COL1,COL2,COL3 data is same for the set of ID numbers(1,2)
Assume the Table name as TABLE1

Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Jul 26, 2010 9:21 pm    Post subject:
Reply with quote

Code:
select a.col1,a.col2,a.col3, sum(b.cost), a.id
from table1 A , table1 B
where a.id = 1 and b.id = 2
and a.col1 = b.col and a.col2 = b.col2  and a.col3 = b.col3
group by a.col1,a.col2,a.col3,a.id

is one way

Code:
select a.col1,a.col2,a.col3,
(select sum(b.cost) from table1 B where a.col1 = b.col and a.col2 = b.col2  and a.col3 = b.col3 and b.id = 2) as cost
, a.id
from table1 A where a.id = 1

is another

and there still are a few other ways to do this.
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 Display ZEDLMSG without Pressing F1 &... anandgbe CLIST & REXX 8 Thu Dec 15, 2016 4:20 pm
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
This topic is locked: you cannot edit posts or make replies. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts How to display value of an exit param... lind sh CICS 2 Sun Oct 09, 2016 12:28 pm


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