Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
venki123

New User

Joined: 05 Feb 2008
Posts: 1
Location: pune

 Posted: Wed Feb 06, 2008 10:03 am    Post subject: JOIN query in DB2 Table A order# receipt line# cost-code Cost-rate 234 1 1 xx .22 234 1 1 xy .11 234 1 1 xz .33 234 1 2 xx .22 234 1 2 xz .33 234 2 1 xx .22 234 3 1 xx .22 234 3 2 xx .22 234 3 2 xy .11 234 3 4 xz .33 Table B order# receipt line# line-qty 234 1 1 10 234 1 2 6 234 2 1 10 234 3 1 9 234 3 2 12 234 3 4 15 I have TWO tables A & B with above colums. Table 1:- Order contains receipt, receipt may contain multiple lines. Again line will contains different associated cost-codes. Table 2:- each line contains quantity I want to calcalualte the one recipt value of for all cost codes associated with it. Eg:- ________1(10)--xx,xy,xz | 1 receipt__________| | |________2(06)--xx,xz. receipt 1 has 2 lines(of qunatity 10 & 6 respectively ) & each line has differnt cost codes. Suppose I want receipt 1 value in the order 234 for cost-code xx. receipt 1 value @ cost code xx :- (10X.22) + (6X.22) {line 1 calculation ) + {line 2 calculation } same for all receipt 1 value @ cost code xx :- (10X.22) + (6X.22) = A receipt 1 value @ cost code xy :- (10X.33) + (6X.33) = B receipt 1 value @ cost code xz :- (10X.11) = C nOW Total receipt value is A+B+c. So that I can calculate all the recips value one by one. Can you give me an idea how can i write a querry for this. Please note this should be in PERFORM loop. Thanks for your help.

bbessa

New User

Joined: 03 Aug 2006
Posts: 13
Location: Brazil

 Posted: Thu Feb 07, 2008 1:31 am    Post subject: Take a look a this article: How to Use GROUP BY with Distinct Aggregates and Derived tables I think it'll help you. Bernardo
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics How to force a select query to abend neo4u DB2 5 Mon Apr 23, 2018 1:36 pm Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am How to replace the below query? pkmurali DB2 12 Tue Feb 27, 2018 9:51 pm Need Guidance regarding UPDATE query Poha Eater DB2 3 Mon Feb 12, 2018 11:35 pm Query to skip Max time-stamp and fetc... vickey_dw DB2 8 Sun Jan 28, 2018 1:09 am

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us