Thanks Pedro again. I am not sure if it is clear enough for you or not. However, If you check two sql statements, things might be easier for you.


Table shippingDiscount
shippingDiscountId, shippingProductId,costAmount,sellAmout,hasCharge,valueType
1;1;500.00;1000.00;1;DISCOUNT
2;1;300.00;500;1;DISCOUNT
3;1;300.00;460.00;0;DISCOUNT
4;1;400.00;600.00;0;DISCOUNT

Before calculate SUM in shippingProduct Table
1;CRUISE;0,0,0,0
After calculate SUM in shippingProduct Table
shippingProductId,productGrup; costChargeAmount,sellChargeAmout,costAmountNoCharge,sellAmountNoCharge
1;CRUISE;800.0,1500.0,700.0,1060.0


two condition  (fields are in shippingDiscount table)
1) charge = 0 AND valueType="DISCOUNT" 
2) charge=1 and valueType="DISCOUNT"



Actually I need to know what components need to implement following two SQL statements in one update operation.


SELECT SUM(costAmt) as costAmount , SUM(sellAmount)  as sellAmount
from shippingProduct as sp, shipppingDiscount as sd
where sp.shippingProductId=sd.shippingProductId and sd.hasCharge=1 and sd.valueType="DISCOUNT"

what component should I use to implement the above sql. costAmount  and sellAmout will update costChargeAmount and sellChargeAmount of shippingProduct table.


SELECT SUM(costAmt) as costAmount , SUM(sellAmount)  as sellAmout
from shippingProduct as sp, shipppingDiscount as sd
where sp.shippingProductId=sd.shippingProductId and sd.hasCharge=0 and sd.valueType="DISCOUNT"

costAmount  and sellAmout will update costNoChargeAmount and sellNoChargeAmount of shippingProduct table.

I would like to have update these four fields in shippingProduct table in one update operation.


costChargeAmount=row1.500+row2.300=800;
sellChargeAmout =1000+500=1500;

while hasCharge=0 and valueType="DISCOUNT"
costAmountNoCharge = 300+400=700 and
sellAmountNoCharge = 460+600=1060


Thanks.
Asif

Thanks.

Could you please elaborate a bit with steps and what componets to use. So that I can create job and test. As you mentioned for two sql do I drop two tMSSQLRow comonent and then what componets to use make one update statement.

Thanks.

Asif

Hi,

Because all calculated fields will be updated to the same table. That is why instead of doing update operation on same table each time rather update all at once. Anyways, It seems very complex. I will update one by one.

Thanks.

Asif