This is a long an involved problem.
I have a join between F4801 and F1201 and F1217 and F4801T – this is the work order super find. This enables me to look at any work orders using equipment information and vise versa. Really nice, but I want to see a unique list of assets, take out the duplicates.
This is easy with a ER to supress grid row, but this is very slow, I want to make it fast. The use case is if an asset as 10000 work orders, that is a lot of work that the web client needs to do (10000 suppresses) before it shows you 1 row, you might be waiting.
So I’m going to create a JDE table, then create a oracle view with the table name and put the complex joins into my oracle view – and then I’ll have a fast and efficient solution with a simple application. Sound good – yes it does!
Problem 1 my table has 386 columns:
There is no way I’m going to create that table, I’ll surely miss one and mess it all up.
So, I’m going to cheat.
I’ve selected out the columns in my existing business view that my existing application is based upon, just the DD id’s of all of them. Note that this was done by saving the view to a par file, turning that into a zip file and opening the spec.zip file within that par file.
This is a long list. I moved this into a spreadsheet and got a distinct list of columns and table names and then removed the duplicate column names:
=+"insert into DV910.F98711 values (0,'F55WOSF2',0,'"&C2&"',"&B2&",'SF"&C2&"',0,'E910','','','','');"
Using the formula above I converted the DD item name into an insert statement into central objects. Sure, people will tell you that you should not do this, but really you also should not look at your phone while you walk. This is not going to kill you.
Note that I’ve not run this yet!!
I then created a table in OMW called F44WOFS2 and make the column prefix SF and added 1 column that I knew did exist. Created my primary key and saved it and checked it in.
Then I deleted the single column from central objects:
delete from dv910.f98711 where tdobnm = 'F55WOSF2';
commit;
And ran all my inserts!
insert into DV910.F98711 values (0,'F55WOSF2',0,'NUMB',1,'SFNUMB',0,'E910','','','','');
insert into DV910.F98711 values (0,'F55WOSF2',0,'DOCO',2,'SFDOCO',0,'E910','','','','');
insert into DV910.F98711 values (0,'F55WOSF2',0,'DCTO',3,'SFDCTO',0,'E910','','','','');
insert into DV910.F98711 values (0,'F55WOSF2',0,'PARS',4,'SFPARS',0,'E910','','','','');
insert into DV910.F98711 values (0,'F55WOSF2',0,'TYPS',5,'SFTYPS',0,'E910','','','','');
insert into DV910.F98711 values (0,'F55WOSF2',0,'PRTS',6,'SFPRTS',0,'E910','','','','');
insert into DV910.F98711 values (0,'F55WOSF2',0,'DL01',7,'SFDL01',0,'E910','','','','');
I then checked out the table and viola!
My table exists with 386 columns. The exact order and sequence and everything. This is nice.
The second part of what I need to do is create an oracle view that matches this format so that when I find on this table, I get the results that I intend to get.
Now before you say “This guy is going too far”, this is pretty much what 21CFR11 does:
CREATE VIEW PRODDTA.F0011 AS SELECT ICICUT , ICICU , ICIST , ICIAPP , ICAICU , ICUSER , ICDICJ , ICNDO , ICBAL , ICBALJ , ICAME , ICDOCN , ICAUSR , ICPOB , ICIBOI , ICAIPT , ICOFFP , ICPID , ICJOBN , ICUPMJ , ICUPMT , ICDRSP , IC52PP , ICCBP FROM PRODDTA.F0011_ADT ;
Mine’s just a little more complex…
create view testdta.F55WOSF2 (SFNUMB,
SFDOCO,
SFDCTO,
SFPARS,
SFTYPS,
SFPRTS,
SFDL01,
SFSTCM,
SFMCU,
SFLOCN,
SFAN8,
SFESSA,
SFPLSU,
SFPLSA,
SFTOPL,
SFTOEM,
SFBGTC,
SFPLOS,
SFPLLB,
SFPLMR,
SFSEQN,
SFVMRS32,
SFVMRS31,
SFCRR,
SFCRRM,
SFCRDC,
SFCURBALM3,
SFCURBALM2,
SFCURBALM1,
SFENTCKS,
SFSRYN,
SFAN8SRM,
SFAN8AS,
SFVEND,
SFREPTM,
SFREPDT,
SFFAILTM,
SFFAILDT,
SFFAILCD,
SFRLOT,
SFMCUCSL,
SFCSLPRT,
SFPRODF,
SFTIMEZONES,
SFDSAVNAME,
SFSEET,
SFSEST,
SFASN2,
SFASN4,
SFCOVGR,
SFGLCNC,
SFGLCCV,
SFLNGP,
SFEXR1,
SFTXA1,
SFREGION,
SFCTR,
SFRSTM,
SFRYIN,
SFENTCK,
SFMTHPR,
SFTMCO,
SFPHN1,
SFAR1,
SFWHO2,
SFPRODM,
SFISSUE,
SFPMTN,
SFVFWO,
SFJBCD,
SFWR20,
SFWR19,
SFWR18,
SFWR17,
SFWR16,
SFWR15,
SFWR14,
SFWR13,
SFWR12,
SFWR11,
SFUOM2,
SFTRAF,
SFSSOQ,
SFSRNK,
SFSRKF,
SFSQOR,
SFSHPP,
SFPRRP,
SFPRJM,
SFOTAM,
SFOBJ,
SFMPRC,
SFMPCE,
SFDUAL,
SFDRAW,
SFD6J,
SFD5J,
SFCRCF,
SFCRCE,
SFCRCD,
SFCHPR,
SFBSEQ,
SFATST,
SFASID,
SFALSE,
SFAID,
SFCTS8,
SFCTS7,
SFCTS4,
SFLEDG,
SFSRCN,
SFSHFT,
SFSCSP,
SFMWDH,
SFLINE,
SFFA8,
SFFA7,
SFFA6,
SFFA5,
SFFA4,
SFFA3,
SFFA2,
SFFA1,
SFFA0,
SFUPMT,
SFJOBN,
SFUPMJ,
SFSBLI,
SFUN,
SFJBST,
SFAFE,
SFKITL,
SFKIT,
SFUNIT,
SFDADS,
SFDADO,
SFDADC,
SFSFC,
SFACL0,
SFACL9,
SFACL8,
SFACL7,
SFACL6,
SFEXR,
SFEX,
SFMSGA,
SFDER,
SFEFTB,
SFPID,
SFADDS,
SFLOC,
SFLCT,
SFUSER,
SFINSI,
SFAIV,
SFINSA,
SFINSM,
SFINSC,
SFINSP,
SFRMK2,
SFRMK,
SFAMF,
SFDEXJ,
SFJCD,
SFLANO,
SFAPOM,
SFPURP,
SFAPUR,
SFPURO,
SFITCO,
SFFINC,
SFAITP,
SFAITY,
SFTXJS,
SFAROQ,
SFARCQ,
SFRSUB,
SFROBJ,
SFRMCU,
SFXSUB,
SFXOBJ,
SFXMCU,
SFDSUB,
SFDOBJ,
SFDMCU,
SFASUB,
SFAOBJ,
SFAMCU,
SFALRC,
SFARPC,
SFAESV,
SFNORU,
SFEQST,
SFDSP,
SFDAJ,
SFDSCC,
SFDL03,
SFDL02,
SFACL5,
SFACL4,
SFACL3,
SFACL2,
SFACL1,
SFSEQ,
SFAAID,
SFAPID,
SFWARJ,
SFWRFL,
SFCRTL,
SFWOYN,
SFFA23,
SFFA22,
SFFA21,
SFFA9,
SFMTRYN,
SFEQPN,
SFAN8DR,
SFVMRS34,
SFREGSTS,
SFMRRYN,
SFINSDTE,
SFSATYP,
SFTERMYN,
SFAN8DL,
SFEFFF,
SFBREV,
SFWOD,
SFLOTN,
SFCRTU,
SFURRF,
SFURAB,
SFURAT,
SFURDT,
SFURCD,
SFZE10,
SFZE09,
SFZE08,
SFZE07,
SFZE06,
SFZE05,
SFZE04,
SFZE03,
SFZE02,
SFZE01,
SFREFN,
SFVINNU,
SFSYEM,
SFCMOD,
SFPRODC,
SFOGNO,
SFSFXO,
SFOKCO,
SFOCTO,
SFOORN,
SFSTRX,
SFRCTO,
SFRORN,
SFANOB,
SFSLSM,
SFPA8,
SFSHAN,
SFADDJ,
SFSHPJ,
SFORDJ,
SFLNID,
SFKCOO,
SFMMCU,
SFCOOWN,
SFPNS,
SFPMPN,
SFDFMDP,
SFWSCHF,
SFMCULT,
SFXEVT,
SFCOCH,
SFKCO,
SFDCT,
SFDOC,
SFRMTHD,
SFISNO,
SFSCALL,
SFVMRS33,
SFHPLF,
SFRACM,
SFOACM,
SFACSU,
SFACSA,
SFESSU,
SFMTAM,
SFLBAM,
SFAMOT,
SFAMMC,
SFAMLC,
SFAPRT,
SFMPOS,
SFSAID,
SFACDN,
SFESDN,
SFXRTO,
SFNTST,
SFTDAY,
SFBOMC,
SFXDSP,
SFPHSE,
SFREAS,
SFPNRQ,
SFRTCH,
SFDRWC,
SFRREV,
SFRKCO,
SFRAT2,
SFRAT1,
SFLOTG,
SFLOTP,
SFCTS1,
SFLTCM,
SFLTLV,
SFPC,
SFPOU,
SFTDT,
SFMOH,
SFRESC,
SFINDC,
SFUNCD,
SFSPRT,
SFRTG,
SFBM,
SFPPFG,
SFPEC,
SFSHTY,
SFTRT,
SFTBM,
SFPBTM,
SFSHNO,
SFUOM,
SFQTYT,
SFSOQS,
SFSOCN,
SFSOBK,
SFUORG,
SFLITM,
SFAITM,
SFITM,
SFHRSA,
SFAMTA,
SFHRSC,
SFHRSO,
SFAMTC,
SFPAYT,
SFBRT,
SFSETC,
SFAMTO,
SFVR02,
SFVR01,
SFWR10,
SFWR09,
SFWR08,
SFWR07,
SFWR06,
SFWR05,
SFWR04,
SFWR03,
SFWR02,
SFWR01,
SFPPDT,
SFDAT,
SFDAP,
SFDRQJ,
SFSTRT,
SFTRDJ,
SFNAN8,
SFANT,
SFDPL,
SFANP,
SFANPA,
SFANSA,
SFANO,
SFPTWO,
SFCO,
SFAISL,
SFBIN,
SFSRST,
SFDCG,
SFSUB)
AS SELECT F1217.WRNUMB,
F4801.WADOCO,
F4801.WADCTO,
F4801.WAPARS,
F4801.WATYPS,
F4801.WAPRTS,
F4801.WADL01,
F4801.WASTCM,
F4801.WAMCU,
F4801.WALOCN,
F4801.WAAN8,
F4801T.WAESSA,
F4801T.WAPLSU,
F4801T.WAPLSA,
F4801T.WATOPL,
F4801T.WATOEM,
F4801T.WABGTC,
F4801T.WAPLOS,
F4801T.WAPLLB,
F4801T.WAPLMR,
F4801T.WASEQN,
F4801T.WAVMRS32,
F4801T.WAVMRS31,
F4801T.WACRR,
F4801T.WACRRM,
F4801T.WACRDC,
F4801T.WACURBALM3,
F4801T.WACURBALM2,
F4801T.WACURBALM1,
F4801T.WAENTCKS,
F4801T.WASRYN,
F4801T.WAAN8SRM,
F4801T.WAAN8AS,
F4801T.WAVEND,
F4801T.WAREPTM,
F4801T.WAREPDT,
F4801T.WAFAILTM,
F4801T.WAFAILDT,
F4801T.WAFAILCD,
F4801T.WARLOT,
F4801T.WAMCUCSL,
F4801T.WACSLPRT,
F4801T.WAPRODF,
F4801T.WATIMEZONES,
F4801T.WADSAVNAME,
F4801T.WASEET,
F4801T.WASEST,
F4801T.WAASN2,
F4801T.WAASN4,
F4801T.WACOVGR,
F4801T.WAGLCNC,
F4801T.WAGLCCV,
F4801T.WALNGP,
F4801T.WAEXR1,
F4801T.WATXA1,
F4801T.WAREGION,
F4801T.WACTR,
F4801T.WARSTM,
F4801T.WARYIN,
F4801T.WAENTCK,
F4801T.WAMTHPR,
F4801T.WATMCO,
F4801T.WAPHN1,
F4801T.WAAR1,
F4801T.WAWHO2,
F4801T.WAPRODM,
F4801T.WAISSUE,
F4801T.WAPMTN,
F4801T.WAVFWO,
F4801T.WAJBCD,
F4801T.WAWR20,
F4801T.WAWR19,
F4801T.WAWR18,
F4801T.WAWR17,
F4801T.WAWR16,
F4801T.WAWR15,
F4801T.WAWR14,
F4801T.WAWR13,
F4801T.WAWR12,
F4801T.WAWR11,
F4801T.WAUOM2,
F4801T.WATRAF,
F4801T.WASSOQ,
F4801T.WASRNK,
F4801T.WASRKF,
F4801T.WASQOR,
F4801T.WASHPP,
F4801T.WAPRRP,
F4801T.WAPRJM,
F4801T.WAOTAM,
F4801T.WAOBJ,
F4801T.WAMPRC,
F4801T.WAMPCE,
F4801T.WADUAL,
F4801T.WADRAW,
F4801T.WAD6J,
F4801T.WAD5J,
F4801T.WACRCF,
F4801T.WACRCE,
F4801T.WACRCD,
F4801T.WACHPR,
F4801T.WABSEQ,
F4801T.WAATST,
F4801T.WAASID,
F4801T.WAALSE,
F4801T.WAAID,
F4801T.WACTS8,
F4801T.WACTS7,
F4801T.WACTS4,
F4801T.WALEDG,
F4801T.WASRCN,
F4801T.WASHFT,
F4801T.WASCSP,
F4801T.WAMWDH,
F4801T.WALINE,
F1201.FAFA8,
F1201.FAFA7,
F1201.FAFA6,
F1201.FAFA5,
F1201.FAFA4,
F1201.FAFA3,
F1201.FAFA2,
F1201.FAFA1,
F1201.FAFA0,
F1201.FAUPMT,
F1201.FAJOBN,
F1201.FAUPMJ,
F1201.FASBLI,
F1201.FAUN,
F1201.FAJBST,
F1201.FAAFE,
F1201.FAKITL,
F1201.FAKIT,
F1201.FAUNIT,
F1201.FADADS,
F1201.FADADO,
F1201.FADADC,
F1201.FASFC,
F1201.FAACL0,
F1201.FAACL9,
F1201.FAACL8,
F1201.FAACL7,
F1201.FAACL6,
F1201.FAEXR,
F1201.FAEX,
F1201.FAMSGA,
F1201.FADER,
F1201.FAEFTB,
F1201.FAPID,
F1201.FAADDS,
F1201.FALOC,
F1201.FALCT,
F1201.FAUSER,
F1201.FAINSI,
F1201.FAAIV,
F1201.FAINSA,
F1201.FAINSM,
F1201.FAINSC,
F1201.FAINSP,
F1201.FARMK2,
F1201.FARMK,
F1201.FAAMF,
F1201.FADEXJ,
F1201.FAJCD,
F1201.FALANO,
F1201.FAAPOM,
F1201.FAPURP,
F1201.FAAPUR,
F1201.FAPURO,
F1201.FAITCO,
F1201.FAFINC,
F1201.FAAITP,
F1201.FAAITY,
F1201.FATXJS,
F1201.FAAROQ,
F1201.FAARCQ,
F1201.FARSUB,
F1201.FAROBJ,
F1201.FARMCU,
F1201.FAXSUB,
F1201.FAXOBJ,
F1201.FAXMCU,
F1201.FADSUB,
F1201.FADOBJ,
F1201.FADMCU,
F1201.FAASUB,
F1201.FAAOBJ,
F1201.FAAMCU,
F1201.FAALRC,
F1201.FAARPC,
F1201.FAAESV,
F1201.FANORU,
F1201.FAEQST,
F1201.FADSP,
F1201.FADAJ,
F1201.FADSCC,
F1201.FADL03,
F1201.FADL02,
F1201.FAACL5,
F1201.FAACL4,
F1201.FAACL3,
F1201.FAACL2,
F1201.FAACL1,
F1201.FASEQ,
F1201.FAAAID,
F1201.FAAPID,
F1201.FAWARJ,
F1201.FAWRFL,
F1201.FACRTL,
F1201.FAWOYN,
F1201.FAFA23,
F1201.FAFA22,
F1201.FAFA21,
F1201.FAFA9,
F1217.WRMTRYN,
F1217.WREQPN,
F1217.WRAN8DR,
F1217.WRVMRS34,
F1217.WRREGSTS,
F1217.WRMRRYN,
F1217.WRINSDTE,
F1217.WRSATYP,
F1217.WRTERMYN,
F1217.WRAN8DL,
F1217.WREFFF,
F1217.WRBREV,
F1217.WRWOD,
F1217.WRLOTN,
F1217.WRCRTU,
F1217.WRURRF,
F1217.WRURAB,
F1217.WRURAT,
F1217.WRURDT,
F1217.WRURCD,
F1217.WRZE10,
F1217.WRZE09,
F1217.WRZE08,
F1217.WRZE07,
F1217.WRZE06,
F1217.WRZE05,
F1217.WRZE04,
F1217.WRZE03,
F1217.WRZE02,
F1217.WRZE01,
F1217.WRREFN,
F1217.WRVINNU,
F1217.WRSYEM,
F1217.WRCMOD,
F1217.WRPRODC,
F1217.WROGNO,
F1217.WRSFXO,
F1217.WROKCO,
F1217.WROCTO,
F1217.WROORN,
F1217.WRSTRX,
F1217.WRRCTO,
F1217.WRRORN,
F1217.WRANOB,
F1217.WRSLSM,
F1217.WRPA8,
F1217.WRSHAN,
F1217.WRADDJ,
F1217.WRSHPJ,
F1217.WRORDJ,
F1217.WRLNID,
F1217.WRKCOO,
F1217.WRMMCU,
F1217.WRCOOWN,
F4801T.WAPNS,
F4801T.WAPMPN,
F4801T.WADFMDP,
F4801T.WAWSCHF,
F4801T.WAMCULT,
F4801T.WAXEVT,
F4801T.WACOCH,
F4801T.WAKCO,
F4801T.WADCT,
F4801T.WADOC,
F4801T.WARMTHD,
F4801T.WAISNO,
F4801T.WASCALL,
F4801T.WAVMRS33,
F4801T.WAHPLF,
F4801T.WARACM,
F4801T.WAOACM,
F4801T.WAACSU,
F4801T.WAACSA,
F4801T.WAESSU,
F4801.WAMTAM,
F4801.WALBAM,
F4801.WAAMOT,
F4801.WAAMMC,
F4801.WAAMLC,
F4801.WAAPRT,
F4801.WAMPOS,
F4801.WASAID,
F4801.WAACDN,
F4801.WAESDN,
F4801.WAXRTO,
F4801.WANTST,
F4801.WATDAY,
F4801.WABOMC,
F4801.WAXDSP,
F4801.WAPHSE,
F4801.WAREAS,
F4801.WAPNRQ,
F4801.WARTCH,
F4801.WADRWC,
F4801.WARREV,
F4801.WARKCO,
F4801.WARAT2,
F4801.WARAT1,
F4801.WALOTG,
F4801.WALOTP,
F4801.WACTS1,
F4801.WALTCM,
F4801.WALTLV,
F4801.WAPC,
F4801.WAPOU,
F4801.WATDT,
F4801.WAMOH,
F4801.WARESC,
F4801.WAINDC,
F4801.WAUNCD,
F4801.WASPRT,
F4801.WARTG,
F4801.WABM,
F4801.WAPPFG,
F4801.WAPEC,
F4801.WASHTY,
F4801.WATRT,
F4801.WATBM,
F4801.WAPBTM,
F4801.WASHNO,
F4801.WAUOM,
F4801.WAQTYT,
F4801.WASOQS,
F4801.WASOCN,
F4801.WASOBK,
F4801.WAUORG,
F4801.WALITM,
F4801.WAAITM,
F4801.WAITM,
F4801.WAHRSA,
F4801.WAAMTA,
F4801.WAHRSC,
F4801.WAHRSO,
F4801.WAAMTC,
F4801.WAPAYT,
F4801.WABRT,
F4801.WASETC,
F4801.WAAMTO,
F4801.WAVR02,
F4801.WAVR01,
F4801.WAWR10,
F4801.WAWR09,
F4801.WAWR08,
F4801.WAWR07,
F4801.WAWR06,
F4801.WAWR05,
F4801.WAWR04,
F4801.WAWR03,
F4801.WAWR02,
F4801.WAWR01,
F4801.WAPPDT,
F4801.WADAT,
F4801.WADAP,
F4801.WADRQJ,
F4801.WASTRT,
F4801.WATRDJ,
F4801.WANAN8,
F4801.WAANT,
F4801.WADPL,
F4801.WAANP,
F4801.WAANPA,
F4801.WAANSA,
F4801.WAANO,
F4801.WAPTWO,
F4801.WACO,
F4801.WAAISL,
F4801.WABIN,
F4801.WASRST,
F4801.WADCG,
F4801.WASUB
FROM TESTDTA.F4801, TESTDTA.F4801T, TESTDTA.F1201, TESTDTA.F1217
WHERE (F1217.WRNUMB = F1201.FANUMB AND F4801.WANUMB = F1201.FANUMB AND F4801.WADOCO = F4801T.WADOCO) ORDER BY F1217.WREQPN ASC ;
Now, the big test – UTB
Yay! I’ve done it…
Now I need to create my complex join to only get the distinct list of assets, but I think that this is going to be the easy part.
I guess I should show that also.
create view testdta.F55WOSF2 (SFNUMB,
SFDOCO,
SFDCTO,
SFPARS,
SFTYPS,
SFPRTS,
SFDL01,
SFSTCM,
…
SFSUB)
AS SELECT F1217.WRNUMB,
F4801.WADOCO,
F4801.WADCTO,
F4801.WAPARS,
F4801.WATYPS,
F4801.WAPRTS,
F4801.WADL01,
F4801.WASTCM,
…
F4801.WASUB
FROM TESTDTA.F4801, TESTDTA.F4801T, TESTDTA.F1201, TESTDTA.F1217
WHERE F1217.WRNUMB = F1201.FANUMB AND F4801.WANUMB = F1201.FANUMB AND F4801.WADOCO = F4801T.WADOCO
AND F4801.WADOCO= (SELECT MAX(WADOCO) from TESTDTA.F4801 innerF4801 where innerF4801.WANUMB=F4801.WANUMB )
ORDER BY F1217.WREQPN ASC ;
As I don’t really care about the WO that I’m choosing, I’m just getting the newest one, as I just want a list of assets at the end of the day.
And now I only get 33 records, which is the list of unique assets that match my criteria.
No comments:
Post a Comment