SQL Update a Multi-Table View using INSTEAD OF Triggers

Yesterday I added another SQL trick to my bag ‘o tricks. I am currently working on fixing EDI invoicing process and I needed a way to delete records from four files based on the same key. I could have wrote RPGLE Free Embedded SQL that would have been about 60 lines of code but wheres the fun in that.

I popped open the Google window and searched on “iseries sql delete from multiple tables”. the second hit directed me to IBM and the fourth to Preform the Impossible: Update a Multi-table View using INSTEAD OF Triggers. What did we did before Google, oh yeah, read a manual!

Impossible, that’s for me. It took me a few minutes to understand and a peek at IBM documentation I had to create a view and then the trigger. Once created any time I delete from the view all associated records in the four files are deleted. Very cool.

After view and trigger created I can run this SQL interactive or embedded.

delete from yourlib.ediinvw
where ihedoc = 0000046475

My new view is EDIINVW and I am deleting by EDI document control number.

To accomplish this I created the view with the below code using SQL Explorer.

create view yourlib.ediinvw as
select ih.ihedoc, id.idedoc, ss.iaedoc, si.isedoc
from yourlib.vedinih ih
join yourlib.vedinid id on id.idedoc = ih.ihedoc
join yourlib.vediniss ss on ss.iaedoc = ih.ihedoc
join yourlib.vedinis si on si.isedoc = ih.ihedoc

The four fields in the Select statement is the key field for each of the four files.

I then attempted to create the delete Trigger. SQL Explorer did not like the syntax so I copied and pasted into the green screen iSeries SQL interactive command line. My guess is that SQL Explorer has an issue with INSTEAD OF DELETE ON.

create trigger yourlib.ediinvdel
Instead of delete on yourlib.ediinvw
referencing old as EdiInvwOld
for each row mode DB2SQL
begin atomic
delete from yourlib.vedinih
where ihedoc = EdiInvwOld.ihedoc;
delete from yourlib.vedinid
where idedoc = EdiInvwOld.idedoc;
delete from yourlib.vedinid
where iaedoc = EdiInvwOld.iaedoc;
delete from yourlib.vedinid
where isedoc = EdiInvwOld.isedoc;
end;

I learned another very cool SQL trick, Thanks IT JUNGLE and Four Hundred Guru. This will come in handy over the next few weeks as I fix the EDI processes.

Have your best day!

~Richard

In programming, as in everything else, to be in error is to be reborn.  ~Alan J. Perlis

iSeries SQL Function Proper Case.....

One of my clients asked me to update the tax processing currently working in VAI System 2000 on the iSeries. The requirement is set up the county tax file and update the customer master with the proper county code based on the city field in the customer master.

Challenges abound, the city field in the customer file is mixed with all capitals, all lower case, proper case and misspellings. And yes, I have to learn how to fill out Florida sales tax form, which is where we find a $5000.00 limit on county tax. Always a twist somewhere.

First I looked for availability of current taxes for the State of Florida with city, county and current tax. I found Tax-Rates.org and for 39.00 bucks with the information needed, I downloaded CSV, changed to Excel and uploaded to the iSeries using Data Transfer To IBM i. This enables me to create the file on the IBM i automatically during the upload.

Once I have the file on the IBM i I have to overcome the case problem with the RMCITY field. There should be a way to do this without any code. I can do it in RPGLE using embedded SQL no problem but know there must be a way with just an SQL UPDATE statement.

Well, sort of correct, SQL does not have a PROPER function but I found this code about Functions with downloadable code, cool. First brush up on Control Statements and Create Function article by Scott Klement. It did not take me to long to figure out. I was tripped up on the definition of the inbound field but soon realized @TCSTRING VARCHAR(100) is the data field being returned.

I opened System i Navigator and expanded Databases so I can see Functions.




Right click and select New - SQL and the following is displayed -

 

Change the following -

Function = PROPER,
Description = Change Case To Proper
Type = VARCHAR,
Length = 100
Check = Same result returned from …....(Deteministic)
Data access = Modifies SQL data
Specific name = PROPER



Select the Parameters tab and then Insert, the following is displayed -




Change the Parameter Name to @TCSTRING, change Type to VARCHAR, Length to 100  and check of Return Null on Null input.




I downloaded the code and opened in notepad. Copied from BEGIN to END. I left out the SQL parameters since we are selecting them in the wizard. There are easier ways to just create the Function from the copied code, but the wizard breaks it out and explains it a little better.





Click on OK and the Wizard closes and the following should be displayed.



Now we can generate the SQL, right click on PROPER and select Generate.

If I need to change a field to be consistent like the city field in customer master I just run the following SQL statement.

update transfer.VARCUST
set rmcity = qgpl.proper(rmcity)

Several things to figure out, why library where the function is located in has to be specified, I thought it would come from the library list. And I did not take to the time to understand all the checkboxes in the wizard. The function performed as I needed and enabled me to continue on with my project.

I finished identifying the missing city county combinations and update the list.

The following SQL updated the county code field in the customer master based on the cross reference FL_CTYTXa file. There is a little problem with the SQL and I am looking for how it should be done correctly but what I created worked and updated the field as desired.

update transfer.varcust set rmcnty =
(select rkcnty
from transfer.VARCNTY, transfer.FL_CTYTXa
where city = rmcity
and salestaxregion = rkdesc
and rmstat = 'FL'
)
where exists
(select *
from transfer.VARCNTY, transfer.FL_CTYTXa
where city = rmcity
and salestaxregion = rkdesc
and rmstat = 'FL'
)

Previously I update the VARCNTY county tax file with the Florida taxes.

Now on to identifying where to put the hook in tax processing to only charge county tax on the first $5000.00 and testing.   

Any suggestions or questions, please send them on.

~Richard

Talk doesn't cook rice.  ~Chinese Proverb

Happy New Year!

Wishing everyone a Happy and Prosperous New Year!

I am hoping you'll share your prosperousness and consider hiring me as your IBM iSeries hardware and software "Information Technologies Specialist".

I am an Information Technologies Manager specializing in installation, maintenance and operational support for IBM iSeries Mid-range Systems hardware and software. I specialize in manufacturing and distribution environments.

I also have extensive EDI installation and maintenance experiences. I have successfully implemented and re-implemented several EDI installations.

I am a successful project manager who creates and delivers projects on time and under budget. I work well as team leader or independently.

I currently work remotely and some on-site, providing daily operational support and custom programming for several clients located in South Florida. My current programming skills include RPGLE, RPG Free and SQL.

I perform IBM iSeries upgrades and have completed many over the years. I have recently accomplished four upgrades from V5R4 to V6R1 successfully on time and under budget. One upgrade included upgrading a Power 5 to Power 7 iSeries machine.

I have a wide range of experiences with hardware and software from PC’s  to the iSeries with a trail of successes behind me.

I am 24 / 7 person who thrives on challenge and providing businesses savings by eliminating inefficiencies, increasing user productivity and utilizing technology to it's fullest.

~Richard

    

SQL update not needed to resolve issue...

Sometimes I really feel like a putz. One of my clients asked me to change a program so that only active records from the Rolox file are displayed during searches. My initial thought is a two second change to either program to use logical file selecting on 'A' status records or a couple of lines of code in the RPG. I was partially right!

The Rolox file was designed by me in 1995 at the request of the owner of the company. He did not want to use Outlook contacts and wanted it in green screen. He also needs three fields to identify who would get a Christmas card, party invitation or wreath sent to an address as well as the standard contact information fields. The client wanted a one stop process for looking up phone numbers by name. 

Simple enough; I created the file with fields as needed and a maintenance program to add, update or change records in the file. There are three different types of records in the Rolox file, entries of people that are neither customers or vendors, customers from VAI VARCUST file and vendors from VAI VAPVEND file.  This process is created on the iSeries in RPG III and worked as required for six years.

I opened the Rolox file and through my due diligence I realized something is not right. I ran some SQL over the files to determine what the current status of the Rolox file and found many discrepancies.

select * from r37files/vapvend where not exists 

 (select * from r37ceudta/rolox where acdel = rxstus and acvend = rxid and 2 = rxecd);

select * from r37files/varcust where not exists 

 (select * from r37ceudta/rolox where rmdel = rxstus and rmcust = rxid and 1 = rxecd); 

There were thousands of records that did not exist in the Rolox file. This should not be the case since both the VAI customer maintenance and vendor maintenance programs were modified to add, change, delete from the Rolox file or at least so I remembered.

I then started looking at the VAI maintenance programs and found that the programs were not in the VAI modified library. This was a little disturbing. Before going any further I call the client and asked him to show me exactly where he was seeing the problem. First I realized that there are multiple menu options for the Rolox, maintain/edit and search. The client is using the search option and has not used the maintain/edit option in years.

   

At this point it all came back to me, back in 2000 I upgraded the client VAI S2K from version 3.2 to 3.7. During the planning stage I took advantage of the upgrade to get rid of the Rolox file and use the VAI customer and vendor files exclusively. The only additional fields are the party, card and wreath fields. Version 3.7 has a new feature called user defined fields. User defined fields can be created for the following files:

                                                           

     1. Setup                                                                 

     2. Item                                                                  

     3. Item Balance                                                          

     4. Vendor                                                                

     5. Chart of Accounts                                                     

     6. Customer                                                              

     7. Contacts                                                              

     8. Ship-To                                                               

     9. Prospect                                                              

    10. Opportunity                                                           

I had completely forgotten that I sold the client on allowing me to create a new inquiry program that would eliminate the Rolox file and only use the customer and vendor master files.

So the fix is a two second fix; I changed the following logical files to select records equal to status active only. They were originally coded as COMP(NE 'D')

-----------------------------------------------------

 VARCST90 - AR CUSTOMER MASTER LOGICAL BY NAME       

-----------------------------------------------------

         R ARCUSTR                   PFILE(VARCUST)  

         K RMNAME                                    

         S RMDEL                     COMP(EQ 'A')    

--------------------------------------------------

VAPVND90 - AP VENDOR MASTER LOGICAL BY NAME       

--------------------------------------------------

        R APVENDR                   PFILE(VAPVEND)

        K ACNAME                                  

        S ACACT                     COMP(EQ 'A')  

Recompiled the logical files and program.

I have done so many installations and modifications over the past ten years for many different clients and employers, I guess this one just slipped by me.

The client is happy and I am not charging him for a two second change that took me all day. While I was doing this I also installed and configured SQL Explorer on my PC and practiced SQL UPDATE, INSERT INTO, WHERE EXISTS, WHERE NOT EXISTS and SELECT. So not a total loss.

I have removed the old menu option and Rolox file from the system as I should have done years ago. 

It is a good feeling to know what I created ten years ago was the right thing to do and will stand the test of time.

~Richard