SQL or RPG
Would it be better to use SQL or RPG, to update one field in the file with a value from another field in the same file? I am not that familiar with SQL, only a beginner. My RPG skills are rusty as I've not written anything in the last 4 years.
Software/Hardware used:
Software/Hardware used:
ASKED:
May 6, 2009 3:22 PM
UPDATED:
May 8, 2009 10:39 AM
Answer Wiki:
Hi,
If you need to update every record in a file using another value from the same record, SQL would be a good option. If this is a one-off exercise, then SQL will be ideal. If this is something that's going to happen more often I would suggest writing a program for it (you could also use embedded SQL if you want).
Use the STRSQL command to start an interactive SQL session.
First use the SELECT command to check whether your statement will give you the expected results :-
(e.g. need to add postage to total price)
SELECT product_code, (total_price + postage), total_price, postage FROM product_file
IF your happy with the results, then you can use the UPDATE command :-
UPDATE product_file SET total_price = (total_price + postage)
This will then update the field total_price with the curent value of total_price + postage.
Hope this helps.
Regards,
Martin Gilbert.
To see all answers submitted to the Answer Wiki: View Answer History.
Hi,
Just another thought – if you only need to update a couple of records you could also use DFU. Use the command UPDDTA.
Regards,
Martin Gilbert.
Is it possible to do a “Multiplacation” in SQL… ? Using the example from Gilly400, instead of adding the Postage stamp, could I multiply by a fixed % instead?
Hi,
Yes, no problem. eg add 10% to total price :-
UPDATE product_file SET total_price = (total_price + (total_price * 0.10))
Or
UPDATE product_file SET total_price = (total_price * 1.10)
Regards,
Martin Gilbert.
Try this…
MyFile contains 3 fields: Cost, Qty, and Total
Cost = 5.00, Qty = 3, Total = 0 (needs to be updated)
Record format name is MyFiler
The code would look like this..
eval Total = Cost * Qty
update MyFiler
That’s it!!!
Tod
Hi,
Same thing as above in SQL would be :-
UPDATE MyFile SET Total = Cost * Qty
Regards,
Martin Gilbert.