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 Update multiple columns

SQL is cool s**t..I love this stuff.... Below SQL uses file created from spreadsheet and updates the min and max fields in the item balance file from one field from uploaded spreadsheet. 

Spreadsheet fields VENDOR, VMFG, CAPITEM, LOC, MINMAX.

update b50files.vinitmb 

set (ifsmin,ifsmax) = (select minmax, minmax from rbryant.upminmax  

where ifcomp = 1 and ifloc = loc and ifitem = capitem) 

where exists (select * from rbryant.upminmax where ifcomp = 1 and ifloc = loc 

                                 and ifitem = capitem)

Updated 04/29/15

I have a task to update several fields in file with calculated amount based on data from a different file. Since I do not write SQL everyday I do not try to remember and use Google. My google search took me to my blog from 2013. Just what I needed and now expanding complexity for future reference.

I need to take the location item price from item price file and reduce by 50% and update cost fields in item balance file. The real trick in my mind is the "exists". I fumbled around and could not get it work until I added the where select exists. 

vinitmb is item balance file and vinpmat is item price file.

ifavg, ifoth and iflst are cost fields being updated.

ifdiv and ifcls are division class fields.

iflcgd, iflcgt and ifuslc are last change fields in case I screw up and need to back out. Of couse all of this is tested with Select statements and then updated test files to make sure.

update r50files.vinitmb 
set iflst = (select (j6lprc - (j6lprc * .50)) as ncost from r50files.vinpmat where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item),
ifavg = (select (j6lprc - (j6lprc * .50)) as ncost from r50files.vinpmat where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item),
ifoth = (select (j6lprc - (j6lprc * .50)) as ncost from r50files.vinpmat 
where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item),
IFLCGD = 20150429,
IFLCGT = 080000,
IFUSLC = 'SQLRB'
where exists (select * from r50files.vinpmat where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item) 
and ifdiv = 9999 and ifcls = 2 

~Richard

One man's crappy software is another man's full time job.  ~Jessica Gaston

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

Moving on to next contract....

After four and half months I have completed 99% of the interfaces between iSeries Island Pacific ERP and MidRetial Allocation application running on SQL.

I  created over 52 programs and/or files that either send or retrieve data in XML using RPGLE, SQLRPGLE and IFS API’s. One of the best aspects of this project is that I was able to teach myself how to read and write directly to the windows file shares. Utilizing embedded SQL is also a lot of fun, the more I coded without the RPG cycle the simpler tasks became.

I think the best aspect of using SQL is the ability quickly adjust the SQL select statements as needs change.

The generic interface map.




The project went live over the weekend with some minor tweaks but overall a success!

Today I start a new contract as EDI Lead / Developer for a large retail chain. I am really looking forward to increasing my EDI skills in a retail environment. My previous EDI experience revolves around distribution and manufacturing supplying the retail vendors with ASN and Warehousing data.

~Richard

There are two ways to write error-free programs; only the third one works.  ~Alan J. Perlis

RPGLE Free SQL update joy.

Got some joy today! Overcame SQL update process and other silly mistakes and now have an XML document. I checked the summarized quantities and record counts and found no issues. Two more programs cloned from this one and I will be ready for full testing. 


I now see all kinds of possibilities but have to keep focused. This is just a contract for interface programs that will only be used for a couple of years. I started thinking service program, procedures, modules, lions ,tigers and bears oh me oh my.

~Richard

If debugging is the process of removing bugs, then programming must be the process of putting them in.  ~Edsger Dijkstra

SQL - Extract decimal portion of a number...

Got a really early start today arriving on site at 6:30am. My current task is to output sales transaction history to three XML documents. The history is a compilation of daily sales from 300 retail stores that are stored on iSeries in a DB2 table.

After some discussion with the IT Manager it was decided that we would add records to a new file during nightly processing. This file will mimic a file currently in the process except for added status and date/time stamp fields for use in the MidRetail processing.

I first have to determine how I can identify sales based on three categories; regular, promo and markdown. The file does not contain proper coding to identify these types of sales, if it did this would be to easy.

One clue I am given is that markdown’s all end with 99 cents and there is a discount code but not consistent. The first question is how do I get at the number to the right of the decimal place. Should be easy I should be able to do this mathematically but do not know the formula. I want to be able to do this with SQL and did not take long to find answers like Floor() and PARSENAME but not exactly what I am looking for. I went over all the BIF’s again and did not see any silver bullets. Thought this was going to be easy, not!

Then I found an obscure post on a forum about CAST function. It was not exactly what I needed but showed promise. After tinkering around with the function taking care of the positive/negative potential issue I was able to come up with the following, vnew is the selling price and vold is the orginal price:

abs(cast(vnew as int)- vnew)   

Now armed with something that will work I created these three statements:

//Regular
Select vstr, vdte, vcls, vven, vsty, vclr, vsiz, vqty, vold, vnew,abs(cast(vnew as int)- vnew) as cents from rbryant/midsalespf
where abs(cast(vnew as int)- vnew) <> .99
order by vstr, vdte, vcls, vven, vsty, vclr;

//Promo
Select vstr, vdte, vcls, vven, vsty, vclr, vsiz, vqty, vold, vnew,abs(cast(vnew as int)- vnew) as cents from rbryant/midsalespf
where  vnew <> vold
order by vstr, vdte, vcls, vven, vsty, vclr;

//Markdown
Select vstr, vdte, vcls, vven, vsty, vclr, vsiz, vqty, vold, vnew,abs(cast(vnew as int)- vnew) as cents from rbryant/midsalespf
where abs(cast(vnew as int)- vnew) = .99
order by vstr, vdte, vcls, vven, vsty, vclr;

I add sum(vqty) and Group by to the equation I have my daily sales summed to store, date, class, vendor, style and color. I add size and I’ll have my summary to the size. I am sure the weekly summary to the color level will be easy.

After analyzing the data a few different ways and counting up records to make sure nothing missed I took my results to the IT Manager. Looks good and we can move forward.

Tomorrow I look into how to add these statements in my RPGLE program to create XML documents.  

~Richard

Fast and successful week...

It was a really good week, it went by very fast and I have managed to get ahead of schedule. I hoped to finish off the week with a home run but best I could do was lay the foundation for a good start next week. ;)

I am unable to see the recently setup MidRetail test server from the test LPAR. So I spent the morning verifying the iSeries TCP and NetServer configuration and testing the QNTC file system. I took my findings to the IT Manager and he reached the same conclusion, there is a DNS problem. Come to find out, the MS DNS server for the test network recently died and new server had to be reconfigured manually. Unfortunately the iSeries entries were missed. I do not have access to the server so have to wait until Monday to have the network guys figure it out.

The iSeries QNTC file system is part of the iSeries Netserver and allows the iSeries programs output stream files, STMF, directly to a Microsoft Windows share. Very cool stuff, easy to setup and really handy when you need to feed your back end data to a PC application.

I have been using MidRetail on the the production server since the iSeries test LPAR is on a different Domain and unable to directly communicate. While waiting on the Microsoft Application test server to be setup I have been moving documents between systems manually to test my XML output. Once we get the connection issues resolved I will be able to install the MidRetail application on the test server and be able to start daily full cycle testing of the completed interface programs.

I finished polishing the Store interface program and moved on to the Sales History interface program. Each program grows in complexity as I work through the project. This interface requires that store sales be summed to Date, Store, Style, Color and Date, Store, Style, Color, Size daily and  Date, Store, Style, Color weekly. The input data is from a file that is populated nightly from sales of over 300 stores. This is looking like a good fit for my first embedded SQL, whoopee!

I may break the task into two programs, daily and weekly, that creates three XML documents. Some consideration will be needed on handling missed Stores and how to process them once data is retrieved.

I have been writing and learning RPGLE Free and now ready for trying my hand at some embedded SQL. I have seen working examples but have never actually coded myself. I do use SQL Explorer on a regular basis to write Select queries to verify data and find it extremely useful. I have a day or two to work out the techniques but if it looks like it will take me too long to figure out I will just fall back to RPGLE.

After a couple of hours with the IT Manager I have enough information to start analyzing data and putting together a plan. I ran a few SQL queries over the file and have a good feeling for what needs to be done.  

~Richard


Three things are certain:
Death, taxes, and lost data.
Guess which has occurred.
~David Dixon, 1998, winning entry of the Haiku Error Messages 21st Challenge by Charlie Varon and Jim Rosenau, sponsored by Salon.com

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