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: 1280
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 Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
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 3 Sun Mar 12, 2017 5:35 pm
No new posts Display FTP Session Count within TSO Yolanda Harvey TSO/ISPF 3 Fri Mar 10, 2017 10:31 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm


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