I have two files on my System i. One is a parts list that includes prices. The other contains new prices. Is there an easy way to get the new prices into the parts list?

Use SEQUEL to do a JOIN UPDATE. When you do a JOIN UPDATE you automatically update a System i file with data from another System i file! (We believe this is unique in the System i world.) Here’s an example:

UPDATE SET((LSTPC.1 NEW_PRICE.2))
SQL(’FROM PARTMAST, NEWPRICE JOIN PRDNO.1 = PRDNO.2′)

Important note: The file you are updating must be the primary (first) file in the SQL statement.

The first parameter in the SET clause is the column name (field) to be updated. In this example, it is the LSTPC (List Price) field. The second parameter in the SET clause assigns the new value to the column name. In this example, we specify that another field, NEW_PRICE, will supply the value. Alternatively, the value parameter could contain a literal value or a derived character or numeric value, depending on your requirements. For instance, you might want to multiply LSTPC by a numeric value to come up with a new List Price.

The SQL FROM clause specifies the two database files required for the operation and the JOIN identifies the fields used to join the files together. The SQL statement in this example is fairly basic. It updates all LSTPC fields in the PARTMAST file. You also could use WHERE and CASE statements to apply conditional updates in your target file.

When you do a profile exchange in Robot/SECURITY, does the Security Audit Journal (QAUDJRN) show actions taken as being carried out by the “original” user profile or the “exchanged to” user profile?

Any changes made by a person using profile exchange show up in QAUDJRN as being carried out by the “exchanged to” or alternate user profile. If you need to investigate further, check the Robot/SECURITY Profile Exchange Activity List to determine who was using the alternate user profile at that time. The activity list also shows actions performed during the exchange.

This entry was posted on Friday, December 7th, 2007 at 10:11 am and is filed under Robot/SECURITY, SEQUEL. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.