|
|
- Name: SQL Dictionary
- Purpose: Used to coordinate the writing of SQL for the transfer of information between two massive systems. Each system had around 1000 tables with close to 25 years of information I only dealt with 3 of the modules and they had in the region of 750,000 records. The dictionary was an application that contained a list of SQL within two MS Access databases and was used to help coordinate the mapping of fields in the old systems to the new systems. I had to write around 500 complicated queries and it was useful to have the search facility to identify which SQL contained which fields. Additionally I asked users to map code values - the old system had lookup values for things like officers and many of these look up values needed to be altered to different codes in new system. Several colleagues were indicating what these changes were and these lists had to be combined into one list that could be used to alter the lookup fields in a number of different locations. SQL Dictionary provided a location where all colleagues could go in and ensure that they were not repeating work others were or had done.
- I'll try and get a picture in due course.
But a fairly typical SQL command
INSERT INTO UNI7LIVE_SBCASE ( SBCREFVAL, ADDRESS, DSCRPN, RLAMND, RECPTD, AGTNAME, OFFICR, SBAREATM, AVCODE, DEPOSD, TARGSERVD, SAMEND, TDECIN, DECIDD, APRSYS, SURDRN, WATERS, FOLDRN, ALTREF, AMOUNTDUE, APPTYP, WORKCATEG, DEEMDREFDEAD )
SELECT ACOLAID_BCAPR.CASEFULLREF, ACOLAID_BCAPR.LOCADDRESS1, ACOLAID_BCAPR.RICHDESCRIPTION, ACOLAID_BCAPR.AMDPLNREC, ACOLAID_BCAPR.RECDATE, ACOLAID_BCAPR.AGNNAME, ACOLAID_BCTRESOF.CODE, ACOLAID_BCARTEAM.CODE, ACOLAID_BCVALDTY.CODE, ACOLAID_BCAPR.DEPOSITDATE, ACOLAID_BCAPR.RSPSTGTDATE, ACOLAID_BCAPR.RSPS1DATE, ACOLAID_BCTDECIN.CODE, ACOLAID_BCAPR.DECISIONDATE, ACOLAID_BCAPR.PLANRESULT, ACOLAID_BCAPR.SURFACEDRAIN, ACOLAID_BCAPR.WATERSUPPLY, ACOLAID_BCAPR.FOULDRAIN, ACOLAID_BCAPR.ALTREF, ACOLAID_BCAPR.SUBFEEDUETOT, ACOLAID_BCAPNOTY.CODE, ACOLAID_BCTAPLIC.CODE, ACOLAID_BCAPR.PLUS9MONTHS
FROM (((((ACOLAID_BCAPR LEFT JOIN ACOLAID_BCTRESOF ON ACOLAID_BCAPR.CASEOFFICER = ACOLAID_BCTRESOF.LOOKUPKEY) LEFT JOIN ACOLAID_BCVALDTY ON ACOLAID_BCAPR.VALIDITYCODE = ACOLAID_BCVALDTY.LOOKUPKEY) LEFT JOIN ACOLAID_BCTAPLIC ON ACOLAID_BCAPR.APPNTYPE = ACOLAID_BCTAPLIC.LOOKUPKEY) LEFT JOIN ACOLAID_BCARTEAM ON ACOLAID_BCAPR.AREATEAM = ACOLAID_BCARTEAM.LOOKUPKEY) LEFT JOIN ACOLAID_BCTDECIN ON ACOLAID_BCAPR.DECISIONTYPE = ACOLAID_BCTDECIN.LOOKUPKEY) LEFT JOIN ACOLAID_BCAPNOTY ON ACOLAID_BCAPR.CASETYPE = ACOLAID_BCAPNOTY.LOOKUPKEY;
4. Architecture - ASPrunner.net / SQL Azure / Azure web application - I was able to hook the backend up to a further MS Access front end and while the information was being coordinated through ASPrunner.NET and because I could manipulate the SQL its order and import directly into MS Access Query objects and create scripts always with the latest information from my colleagues. I built tools in MS Access using VBA (because I'm confident in it) that helped autogenerate the SQL.
|
