venki123
New User
Joined: 05 Feb 2008 Posts: 1 Location: pune




Table A
order# receipt line# costcode Costrate
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# lineqty
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 costcodes.
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 costcode 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.

