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"
