Thursday, February 21, 2013

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

Thursday, February 14, 2013

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

Clean iSeries output queue by days to keep

It’s has been a fast six months, new job has kept me hopping. One day I’m redesigning the EDI guidelines and RPGLE programming interfaces to the next day chasing Purchase order suggestions. Tape backup failing without notification to GL year end posting to rewriting code replacing OPNQRYF with embedded SQL. Point of Sale hiccups to AP check failure to working with eCommerce. Every single modification that the previous software vendor implemented has a problem one way or the other.

Basically I have not been happier than any other point in my career!  

I ran across the need to automate purging old spool file records and could not find any code close enough to cut, paste, compile, run and free. In the past have used TAATOOL but is not currently an option.

My good buddy Rick Santiago has a purge out queue by date program that he wrote a while back. Short and sweet I quickly envision modifying for my needs.

Through iSeries navigator I created a table with four fields, out queue name, out queue library, number of days to keep spool file entries and user data. 




I also added the following key constraints, C_OUTQUE, C_OUTLIB and C_USRDTA as unique.

Follow your nose and instructions in the comments of the CL and you’ll have a quick little program you can add to your nightly process.

Hopefully this help some other poor one man band quickly solve a minor annoyance programmatically.

CLNOUTQ is the driver and CLNOUTQ2 exists due to only being able to use one RCVF in a CL in a read loop and detect end of file.


CLNOUTQ

CLNOUTQ2


~Richard

Try to discover
The road to success
And you'll seek but never find,
But blaze your own path
And the road to success
Will trail right behind.
~Robert Brault, www.robertbrault.com

                                                     

Tuesday, February 5, 2013

Top 10 Reasons to Switch from IBM iSeries to Oracle and .Net

    1. Your IT budget is small and you want to triple it.

    1. You have large empty room and can not justify hiring more RPGLE programmers to fill it.

    1. Your CEO likes pretty GUI interface. (Who cares if its productive? So what if it takes 20 pretty screens to fill one ugly green screen.)

    1. You have lots of empty rack space for MS servers or want to build out additional rack space.  (One Power 7 rack is not impressive enough to show visitors how important you are.)

    1. Your part time iSeries operator is bored and needs company. (He'll have at least three new friends that will spend days preparing Oracle fixes and nights applying them.)

    1. You need to be seen as an humanitarian and hire a dozens under paid Indian H1B visa holders to fill your empty cubes.

    1. You have the need to field a Cricket team. (You'll have one with all the Indian .net programmers you hired.)

    1. Your bored with RPGLE because it works so well and feel the need to chase .NET bugs for the next 10 years. RPGLE Development is to fast.

    1. You need to feel wanted by causing as much production downtime as possible. (You can always blame the third party software vendor.)

    And the number one reason to switch from iSeries to Oracle……..

    You need 50 programmers to worship you and IT budget bigger than some countries GDP. You are a God!


     ~Richard