Here is another shot... 1. Output to a text file 2. Create a crosstab command of SSN down the side and columns across the top. 3. Output to screen 4. Create table for loading data (project payroll with count = 0, eliminate columns not needed) 5. Load text into a table (May require loading as fixed width on columns) 6. Clean up any excess rows of data. (Header, Total line, etc) 7. Add missing columns 8. Update missing data to each ssn
Gary -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Albert Berry Sent: Wednesday, June 09, 2010 1:13 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Followup on "Row Length Exceeds 4096 characters"error This is a long shot, David, but have you tried to group by SSN only? SELECT MAX(ColleagueID),+ MAX(PP),+ MAX(LastName),+ MAX(FirstName),+ MAX(SEX),+ SSNo, + SUM(Hours),+ etc. + GROUP BY SSNo David Blocker wrote: > > > This is a follow-up to the problem I wrote about with a view no longer > worked apparently because the stored CREATE VIEW command was more than > 4096 characters and couldn't be stored in SYS_VIEWS. > > > > In working out a workaround for this issue, I created a temporary > table and attempted to INSERT data into the table using the same > SELECT I had tried to use for the View. Surprise! It failed with the > same error message that "Row length exceeds maximum of 4.096 > characters". So I then tried DECLARE CURSOR to that SELECT and it > STILL gave me the same error message. In fact, the SELECT fails at > the R> prompt with the same error message! > > > > The SELECT in question is pasted to the end of this email. As I > indicated, the format of the query is: > > SELECT ColleagueID, PP, LastName, FirstName, SEX, SSNO, + > > SUM(Col1),+ > > SUM(Col2),+ > > SUM(Col3),+ > > (up to a total of 258 SUM expressions) + > > GROUP BY ColleagueID, PP, LastName, FirstName, SEX, SSNO > > > > All of the SUM's except one which is NUMERIC (9,2) are CURRENCY columns. > > > > I find that if I removed the last 7 SUM's, the query worked (total of > 251 SUM's), but as soon as I added ONE more, it failed. > > > > In an attempt to make the "row" narrower, I REMOVED 5 of the 6 fields > in the SELECT and GROUP BY phrases (these columns are all text, a > total of 66 bytes) and found I could add back in SIX of the SUM's I > had removed, but the 7^th made it crash again. > > > > So I'm back to the original question I asked: What exactly is the > limitation we're talking about?? It's not a ROW in the sense of ROW > in a TABLE, because the table PAYROLL I'm summing from has 280 > columns, including the 258 columns that are being summed and several > other CURRENCY and TEXT columns!! > > > > Razzak, can you clarify? > > > > David Blocker > > > > The SELECT that fails: > > > > SELECT ColleagueID, PP, LastName, FirstName, SEX, SSNO, + > > SUM(Hours),+ > > SUM(a12CL),+ > > SUM(a12DM),+ > > SUM(a12MT),+ > > SUM(a12NO),+ > > SUM(a12OH),+ > > SUM(a12PI),+ > > SUM(a12RT),+ > > SUM(a12UN),+ > > SUM(a12W),+ > > SUM(a457P),+ > > SUM(a5WK),+ > > SUM(ADDL),+ > > SUM(BERE),+ > > SUM(BMFF),+ > > SUM(BONS),+ > > SUM(BPC),+ > > SUM(CH26),+ > > SUM(CLAS),+ > > SUM(CLSD),+ > > SUM(CMFO),+ > > SUM(CMSL),+ > > SUM(CMSU),+ > > SUM(COMM),+ > > SUM(CONTI),+ > > SUM(CTYM),+ > > SUM(ENGR),+ > > SUM(ENSB),+ > > SUM(EVNT),+ > > SUM(EXBN),+ > > SUM(FS20),+ > > SUM(FS26),+ > > SUM(FSHP),+ > > SUM(HOL), + > > SUM(JURY),+ > > SUM(MAPT),+ > > SUM(MEET),+ > > SUM(MIST),+ > > SUM(MNOT),+ > > SUM(OFFC),+ > > SUM(OFFH),+ > > SUM(OSB),+ > > SUM(OT),+ > > SUM(OT1),+ > > SUM(OTHRe),+ > > SUM(PEER),+ > > SUM(PERF),+ > > SUM(PRIV),+ > > SUM(PROA),+ > > SUM(PTO),+ > > SUM(PTOC),+ > > SUM(RCLS),+ > > SUM(REFL),+ > > SUM(REG),+ > > SUM(RELO),+ > > SUM(RETR),+ > > SUM(RETS),+ > > SUM(RPVT),+ > > SUM(RUEL) + > > SUM(SEV),+ > > SUM(SPAS),+ > > SUM(SPEC),+ > > SUM(STDP),+ > > SUM(TRAV),+ > > SUM(VPBN),+ > > SUM(VPRO),+ > > SUM(WKSP),+ > > SUM(YOS),+ > > SUM(YOTM),+ > > SUM(ColleagueGross),+ > > SUM(RBaseCalcGross),+ > > SUM(CONN),+ > > SUM(DHA2),+ > > SUM(DHAF), + > > SUM(DHAI),+ > > SUM(DHB2),+ > > SUM(DHBF),+ > > SUM(DHBI),+ > > SUM(DHF2),+ > > SUM(DHFF),+ > > SUM(DHFI),+ > > SUM(DHS2),+ > > SUM(DHSF),+ > > SUM(DHSI),+ > > SUM(DLA2),+ > > SUM(DLAF),+ > > SUM(DLAI),+ > > SUM(DLB2),+ > > SUM(DLBF),+ > > SUM(DLBI),+ > > SUM(DLF2),+ > > SUM(DLFF),+ > > SUM(DLFI),+ > > SUM(DLS2),+ > > SUM(DLSF),+ > > SUM(DLSI),+ > > SUM(DNA2),+ > > SUM(DNAF),+ > > SUM(DNAI),+ > > SUM(DNB2),+ > > SUM(DNBF),+ > > SUM(DNBI),+ > > SUM(DNF2),+ > > SUM(DNFF),+ > > SUM(DNFI),+ > > SUM(DNS2),+ > > SUM(DNSF),+ > > SUM(DNSI),+ > > SUM(FSAD),+ > > SUM(FSAM),+ > > SUM(HMA2),+ > > SUM(HMAF),+ > > SUM(HMAI),+ > > SUM(HMB2),+ > > SUM(HMBF),+ > > SUM(HMBI),+ > > SUM(HMF2),+ > > SUM(HMFF),+ > > SUM(HMFI),+ > > SUM(HMS2),+ > > SUM(HMSF),+ > > SUM(HMSI),+ > > SUM(HVA2),+ > > SUM(HVAF),+ > > SUM(HVAI),+ > > SUM(HVB2),+ > > SUM(HVBF),+ > > SUM(HVBI),+ > > SUM(HVF2),+ > > SUM(HVFF),+ > > SUM(HVFI),+ > > SUM(HVS2),+ > > SUM(HVSF),+ > > SUM(HVSI),+ > > SUM(I457),+ > > SUM(I45V),+ > > SUM(INDV),+ > > SUM(INFT),+ > > SUM(INNED),+ > > SUM(INPT),+ > > SUM(PKEE),+ > > SUM(PKNG),+ > > SUM(PPA2),+ > > SUM(PPAF),+ > > SUM(PPAI),+ > > SUM(PPB2),+ > > SUM(PPBF),+ > > SUM(PPBI),+ > > SUM(PPF2),+ > > SUM(PPFF),+ > > SUM(PPFI),+ > > SUM(PPS2),+ > > SUM(PPSF),+ > > SUM(PPSI),+ > > SUM(S125),+ > > SUM(T457),+ > > SUM(T45V),+ > > SUM(TCDV),+ > > SUM(TCFT),+ > > SUM(TCNE),+ > > SUM(TCPT),+ > > SUM(TREE),+ > > SUM(RBaseAdjGross),+ > > SUM(CADI),+ > > SUM(CASM),+ > > SUM(CASS),+ > > SUM(FWTE),+ > > SUM(FWTM),+ > > SUM(FWTS),+ > > SUM(MAHH),+ > > SUM(MAST),+ > > SUM(MEDI),+ > > SUM(NRAF),+ > > SUM(SSEC),+ > > SUM(VAST),+ > > SUM(ADDE),+ > > SUM(ADDS),+ > > SUM(AFT),+ > > SUM(AFTP),+ > > SUM(DDAI),+ > > SUM(DDBI),+ > > SUM(DDF2),+ > > SUM(DDFF),+ > > SUM(DDS2),+ > > SUM(DDSF),+ > > SUM(DDSI),+ > > SUM(DHDA),+ > > SUM(DHDB),+ > > SUM(DHDF),+ > > SUM(DHDS),+ > > SUM(DLC2),+ > > SUM(DLCH),+ > > SUM(DLDA),+ > > SUM(DLDB),+ > > SUM(DLDF),+ > > SUM(DLDS),+ > > SUM(DLS1),+ > > SUM(DLS3),+ > > SUM(DLSC),+ > > SUM(DLSP),+ > > SUM(GARN),+ > > SUM(GIFT),+ > > SUM(HDAI),+ > > SUM(HDBI),+ > > SUM(HDF2),+ > > SUM(HDS2),+ > > SUM(HVDA),+ > > SUM(HVDB),+ > > SUM(HVDF),+ > > SUM(HVDI),+ > > SUM(INFR),+ > > SUM(INNR),+ > > SUM(INPR),+ > > SUM(INRV),+ > > SUM(LEVI),+ > > SUM(LF10),+ > > SUM(LF1X),+ > > SUM(LF20),+ > > SUM(LF2X),+ > > SUM(LF30),+ > > SUM(LF3X),+ > > SUM(LF40),+ > > SUM(LF50),+ > > SUM(Mas1),+ > > SUM(MBTA),+ > > SUM(OINS),+ > > SUM(OTHRd),+ > > SUM(PARK),+ > > SUM(PAYC),+ > > SUM(PDAI),+ > > SUM(PDBI),+ > > SUM(PDF2),+ > > SUM(PDFF),+ > > SUM(PDSI),+ > > SUM(TCFR),+ > > SUM(TCNR),+ > > SUM(TCPR),+ > > SUM(TCRV),+ > > SUM(ColleagueNetPay),+ > > SUM(RBaseCalcNetPay),+ > > SUM(INDVM),+ > > SUM(INFRM),+ > > SUM(INFTM),+ > > SUM(INNRM),+ > > SUM(INPRM),+ > > SUM(INPTM),+ > > SUM(INRVM),+ > > SUM(TCDVM),+ > > SUM(TCFRM),+ > > SUM(TCFTM),+ > > SUM(TCNRM),+ > > SUM(TCPRM),+ > > SUM(TCPTM),+ > > SUM(TCRVM),+ > > SUM(FRNG),+ > > SUM(LIF1),+ > > SUM(LIF2),+ > > SUM(LIFF) + > > FROM PAYROLL + > > GROUP BY ColleagueID, PP, LastName, FirstName, SEX, SSNo > > > > * > * > > "David Blocker > [email protected] > Office: 781-344-1920 > Cell: 339-206-0261"
