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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm

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