I created an external table DDL to import a fixed width file with 326
columns. To move that data in to the internal table, I was going to
select from a view. I want the view to trim trailing blanks and to
convert the file's various date formats to Firebird dates.
Without any data conversion the view can be created without any problem.
When I try to trim all text fields that are longer than one character, I
get an error that the table definition, at 65574 bytes, is too large.
The strange part is that originally I was left and right trimming ALL
columns. The error gave the same table size; only left trimming
specific columns didn't reduce the size at all.

I have two questions, what can I do to reduce the size of the metadata,
and if I can't use a view how should I transform the data when importing
in to the internal table?

ISC ERROR CODE:335544351
ISC ERROR MESSAGE: unsuccessful metadata update new record size of 65574
bytes is too big TABLE V_THE_FILE

CREATE VIEW V_THE_FILE
(
RXCLAIMNBR, CLMSEQNBR, CLAIMSTS, CARRIERID, SCARRIERID, CARRPROC,
ACCOUNTID, GROUPID,
GROUPPLAN, GRPCLIBENF, GROUPSIC, CLMRESPSTS, MEMBERID, MBRLSTNME,
MBRFSTNME, MBRMDINIT,
MBRPRSNCDE, MBRRELCDE, MBRSEX, MBRBIRTH, MBRAGE, MBRZIP, SOCSECNBR,
DURKEY, DURFLAG,
MBRFAMLYID, MBRFAMLIND, MBRFAMLTYP, MBRPLAN, MBRPRODCDE, MBRRIDERCD,
CARENETID, CAREQUALID,
CAREFACID, CAREFACNAM, MBRPCPHYS, PPRSFSTNME, PPRSLSTNME, PPRSMDINIT,
PPRSSPCCDE, MBRALTINFL,
MBRALTINCD, MBRALTINID, MBRMEDTYPE, MBRHICCDE, CARDHOLDER, PATLASTNME,
PATFRSTNME, PERSONCDE,
RELATIONCD, SEXCODE, BIRTHDTE, ELIGCLARIF, CUSTLOC, PRMCAREPRV,
PRMCAREPRQ, FACILITYID,
OTHCOVERAG, BINNUMBER, PROCESSOR, GROUPNBR, VERSIONNBR, TRANSCDE,
DATESBM, TIMESBM, ORGPDSBMDT,
RVDATESBM, CLMCOUNTER, GENERICCTR, FORMLRYCTR, RXNUMBER, RXNUMBERQ,
REFILL, DISPSTATUS,
DTEFILLED, COMPOUNDCD, PRODTYPCDE, PRODUCTID, PRODUCTKEY, DECIMALQTY,
DAYSSUPPLY, PSC,
WRITTENDTE, NBRFLSAUTH, ORIGINCDE, DENIALCLAR, PAMCNBR, PAMCCDE,
PRAUTHNBR, PRAUTHRSN,
PRAUTHFDTE, PRAUTHTDTE, LABELNAME, PRODNAME, DRUGMFGRID, DRUGMFGR,
GPINUMBER, GENERICNME,
DDID, GCN, GCNSEQ, KDC, AHFS, DRUGDEACOD, RXOTCIND, MULTSRCCDE,
GENINDOVER, PRDREIMIND,
BRNDTRDNME, FDATHERAEQ, METRICSTRG, DRGSTRGUOM, ADMINROUTE, DOSAGEFORM,
MNTDRUGCDE, THIRDPARTYX,
DRGUNITDOS, SBMUNITDOS, ALTPRODTYP, ALTPRODCDE, RXNETWORK, RXNETWRKNM,
REGIONCDE, SRVPROVID,
SRVPROVIDQ, SRVPROVNME, PROVLOCKQL, PROVLOCKID, STORENBR, AFFILIATIN,
PAYEEID, DISPRCLASS,
DISPROTHER, PHARMZIP, PRESNETWID, PRESCRIBER, PRESCRIDQL, PRESLSTNME,
PRESFSTNME, PRESMDINIT,
PRESSPCCDE, FNLPLANCDE, FNLPLANDTE, PLANQUAL, PLNNDCLIST, PLNGPILIST,
PLANDRUGST, PLANFRMLRY,
PLNFNLPSCH, FORMULARY, FORMLRFLAG, CONTHERAPY, MESSAGE1, MESSAGE2,
MESSAGE3, REJCNT,
REJCDE1, REJCDE2, REJCDE3, RJCPLANID, DURCONFLCT, DURINTERVN,
DUROUTCOME, LVLSERVICE,
DIAGNOSIS, DIAGNOSISQ, RVDURCNFLC, RVDURINTRV, RVDUROUTCM, RVLVLSERVC,
DRGCNFLCT1, SEVERITY1,
OTHRPHARM1, DTEPRVFIL1, QTYPRVFIL1, DATABASE1, OTHRPRESC1, FREETEXT1,
DRGCNFLCT2, SEVERITY2,
OTHRPHARM2, DTEPRVFIL2, QTYPRVFIL2, DATABASE2, OTHRPRESC2, FREETEXT2,
DRGCNFLCT3, SEVERITY3,
OTHRPHARM3, DTEPRVFIL3, QTYPRVFIL3, DATABASE3, OTHRPRESC3, FREETEXT3,
PRICESCHED, PRICETABLE,
FEETYPE, AWPUNITCST, WACUNITCST, GEAPUNTCST, COSTTYPCDE, COSTSOURCE,
CTYPEUCOST, BASISCOST,
RVINCNTVSB, SBMINGRCST, SBMDISPFEE, SBMSLSTAX, SBMPATPAY, SBMAMTDUE,
SBMINCENTV, SBMPROFFEE,
SBMTOTHAMT, USUALNCUST, DENIALDTE, OTHRPAYOR, CALINGRCST, CALDISPFEE,
CALSLSTAX, CALPATPAY,
CALDUEAMT, CALWITHHLD, CALFCOPAY, CALPCOPAY, CALCOPAY, CALPRODSEL,
CALATRTAX, CALEXCEBFT,
CALINCENTV, CALATRDED, CALTOTHAMT, CALPROFFEE, CLALOTHPAYA, CALCOSTSRC,
APPINGRCST, APPDISPFEE,
APPSLSTAX, APPPATPAY, APPDUEAMT, APPWITHHLD, APPFCOPAY, APPPCOPAY,
APPCOPAY, APPPRODSEL,
APPATRTAX, APPEXCEBFT, APPINCENTV, APPATRDED, APPTOTHAMT, APPPROFFEE,
APPOTHPAYA, APPCOTSRC,
APPCOSTTYP, APPPRCTYPE, POSINGRCST, POSDISPFEE, POSSLSTAX, POSPATPAY,
POSDUEAMT, POSWITHHLD,
POSCOPAY, POSPRODSEL, POSATRTAX, POSEXCEBFT, POSINCENTV, POSATRDED,
POSTOTHAMT, POSPROFFEE,
POSOTHPAYA, POSCOTSRC, RSPREIMBUR, RSPINGRCST, RSPDISPFEE, RSPSLSTAX,
RSPPATPAY, RSPDUEAMT,
RSPFCOPAY, RSPPCOPAY, RSPCOPAY, RSPPRODSEL, RSPATRTAX, RSPEXCEBFT,
RSPINCENTV, RSPATRDED,
RSPTOTHAMT, RSPPROFFEE, RSPOTHPAYA, RSPACCUDED, RSPREMBFT, RSPREMDED,
RSPPLANID, RBLINGRCST,
RBLDISPFEE, RBLSLSTAX, RBLPATPAY, RBLDUEAMT, RBLWITHHLD, RBLFCOPAY,
RBLPCOPAY, RBLCOPAY,
RBLPRODSEL, RBLATRTAX, RBLEXCEBFT, RBLINCENTV, RBLATRDED, RBLTOTHAMT,
RBLPROFFEE, RBLOTHPAYA,
RBLCOTSRC, RBLCOSTTYP, RBLPRCTYPE, PRODAYSSUP, PROQTY, INDDEDPTD,
FAMDEDPTD, DEDFLAG,
BFTMAXFLAG, REBILLFLAG, OOPFLAG, RXTFLAG, REIMBURSMT, CLMORIGIN,
HLDCLMFLAG, HLDCLMDAYS, DTEINJURY
) AS
SELECT
RXCLAIMNBR, CLMSEQNBR, CLAIMSTS, RTRIM(CARRIERID), RTRIM(SCARRIERID),
RTRIM(CARRPROC),
RTRIM(ACCOUNTID), RTRIM(GROUPID), RTRIM(GROUPPLAN), RTRIM(GRPCLIBENF),
RTRIM(GROUPSIC), CLMRESPSTS, RTRIM(MEMBERID),
RTRIM(MBRLSTNME), RTRIM(MBRFSTNME), MBRMDINIT, RTRIM(MBRPRSNCDE),
MBRRELCDE, MBRSEX, RTRIM(MBRBIRTH), MBRAGE,
RTRIM(MBRZIP), RTRIM(SOCSECNBR), RTRIM(DURKEY), DURFLAG,
RTRIM(MBRFAMLYID), MBRFAMLIND, MBRFAMLTYP,
RTRIM(MBRPLAN), RTRIM(MBRPRODCDE), RTRIM(MBRRIDERCD),
RTRIM(CARENETID),
RTRIM(CAREQUALID), RTRIM(CAREFACID), RTRIM(CAREFACNAM),
RTRIM(MBRPCPHYS), RTRIM(PPRSFSTNME), RTRIM(PPRSLSTNME), PPRSMDINIT,
RTRIM(PPRSSPCCDE), MBRALTINFL,
RTRIM(MBRALTINCD), RTRIM(MBRALTINID), MBRMEDTYPE, RTRIM(MBRHICCDE),
RTRIM(CARDHOLDER), RTRIM(PATLASTNME), RTRIM(PATFRSTNME),
RTRIM(PERSONCDE), RELATIONCD, SEXCODE,
RTRIM(BIRTHDTE), ELIGCLARIF, RTRIM(CUSTLOC), RTRIM(PRMCAREPRV),
RTRIM(PRMCAREPRQ), RTRIM(FACILITYID), OTHCOVERAG, RTRIM(BINNUMBER),
RTRIM(PROCESSOR), RTRIM(GROUPNBR), RTRIM(VERSIONNBR),
RTRIM(TRANSCDE), RTRIM(DATESBM), RTRIM(TIMESBM), RTRIM(ORGPDSBMDT),
RTRIM(RVDATESBM), CLMCOUNTER, GENERICCTR, FORMLRYCTR, RTRIM(RXNUMBER),
RXNUMBERQ, RTRIM(REFILL),
DISPSTATUS, RTRIM(DTEFILLED), COMPOUNDCD, RTRIM(PRODTYPCDE),
PRODUCTID, RTRIM(PRODUCTKEY), DECIMALQTY, DAYSSUPPLY, PSC,
RTRIM(WRITTENDTE),
NBRFLSAUTH, ORIGINCDE, RTRIM(DENIALCLAR), RTRIM(PAMCNBR), PAMCCDE,
RTRIM(PRAUTHNBR), RTRIM(PRAUTHRSN), RTRIM(PRAUTHFDTE),
RTRIM(PRAUTHTDTE), RTRIM(LABELNAME),
RTRIM(PRODNAME), RTRIM(DRUGMFGRID), RTRIM(DRUGMFGR), RTRIM(GPINUMBER),
RTRIM(GENERICNME), DDID, GCN, GCNSEQ, KDC, RTRIM(AHFS), DRUGDEACOD,
RXOTCIND, MULTSRCCDE,
GENINDOVER, PRDREIMIND, BRNDTRDNME, RTRIM(FDATHERAEQ), METRICSTRG,
RTRIM(DRGSTRGUOM), RTRIM(ADMINROUTE), RTRIM(DOSAGEFORM), MNTDRUGCDE,
THRDPARTYX,
DRGUNITDOS, SBMUNITDOS, ALTPRODTYP, RTRIM(ALTPRODCDE),
RTRIM(RXNETWORK), RTRIM(RXNETWRKNM), RTRIM(REGIONCDE), RTRIM(SRVPROVID),
RTRIM(SRVPROVIDQ), RTRIM(SRVPROVNME),
PROVLOCKQL, RTRIM(PROVLOCKID), RTRIM(STORENBR), RTRIM(AFFILIATIN),
RTRIM(PAYEEID), RTRIM(DISPRCLASS), RTRIM(DISPROTHER), RTRIM(PHARMZIP),
RTRIM(PRESNETWID), RTRIM(PRESCRIBER),
RTRIM(PRESCRIDQL), RTRIM(PRESLSTNME), RTRIM(PRESFSTNME), PRESMDINIT,
RTRIM(PRESSPCCDE), RTRIM(FNLPLANCDE), RTRIM(FNLPLANDTE),
RTRIM(PLANQUAL), RTRIM(PLNNDCLIST), RTRIM(PLNGPILIST),
PLANDRUGST, PLANFRMLRY, RTRIM(PLNFNLPSCH), FORMULARY, FORMLRFLAG,
CONTHERAPY, RTRIM(MESSAGE1), RTRIM(MESSAGE2), RTRIM(MESSAGE3), REJCNT,
RTRIM(REJCDE1), RTRIM(REJCDE2),
RTRIM(REJCDE3), RTRIM(RJCPLANID), RTRIM(DURCONFLCT),
RTRIM(DURINTERVN), RTRIM(DUROUTCOME), RTRIM(LVLSERVICE),
RTRIM(DIAGNOSIS), RTRIM(DIANOSISQ), RTRIM(RVDURCNFLC),
RTRIM(RVDURINTRV), RTRIM(RVDUROUTCM),
RTRIM(RVLVLSERVC), RTRIM(DRGCNFLCT1), SEVERITY1, OTHRPHARM1,
RTRIM(DTEPRVFIL1), QTYPRVFIL1, DATABASE1, OTHRPRESC1, RTRIM(FREETEXT1),
RTRIM(DRGCNFLCT2),
SEVERITY2, OTHRPHARM2, RTRIM(DTEPRVFIL2), QTYPRVFIL2, DATABASE2,
OTHRPRESC2, RTRIM(FREETEXT2), RTRIM(DRGCNFLCT3), SEVERITY3, OTHRPHARM3,
RTRIM(DTEPRVFIL3), QTYPRVFIL3, DATABASE3, OTHRPRESC3,
RTRIM(FREETEXT3), RTRIM(PRICESCHED), RTRIM(PRICETABLE), RTRIM(FEETYPE),
AWPUNITCST, WACUNITCST,
GEAPUNTCST, RTRIM(COSTTYPCDE), COSTSOURCE, CTYPEUCOST,
RTRIM(BASISCOST), RVINCNTVSB, SBMINGRCST, SBMDISPFEE, SBMSLSTAX,
SBMPATPAY,
SBMAMTDUE, SBMINCENTV, SBMPROFFEE, SBMTOTHAMT, USUALNCUST,
RTRIM(DENIALDTE), OTHRPAYOR, CALINGRCST, CALDISPFEE, CALSLSTAX,
CALPATPAY, CALDUEAMT, CALWITHHLD, CALFCOPAY, CALPCOPAY, CALCOPAY,
CALPRODSEL, CALATRTAX, CALEXCEBFT, CALINCENTV, CALATRDED,
CALTOTHAMT, CALPROFFEE, CALOTHPAYA, CALCOSTSRC, APPINGRCST,
APPDISPFEE, APPSLSTAX, APPPATPAY, APPDUEAMT, APPWITHHLD,
APPFCOPAY, APPPCOPAY, APPCOPAY, APPPRODSEL, APPATRTAX, APPEXCEBFT,
APPINCENTV, APPATRDED, APPTOTHAMT, APPPROFFEE,
APPOTHPAYA, APPCOTSRC, RTRIM(APPCOSTTYP), RTRIM(APPPRCTYPE),
POSINGRCST, POSDISPFEE, POSSLSTAX, POSPATPAY, POSDUEAMT, POSWITHHLD,
POSCOPAY, POSPRODSEL, POSATRTAX, POSEXCEBFT, POSINCENTV, PSTATRDED,
POSTOTHAMT, POSPROFFEE, POSOTHPAYA, POSCOSTSRC,
RTRIM(RSPREIMBUR), RSPINGRCST, RSPDISPFEE, RSPSLSTAX, RSPPATPAY,
RSPDUEAMT, RSPFCOPAY, RSPPCOPAY, RSPCOPAY, RSPPRODSEL,
RSPATRTAX, RSPEXCEBFT, RSPINCENTV, RSPATRDED, RSPTOTHAMT, RSPPROFEE,
RSPOTHPAYA, RSPACCUDED, RSPREMBFT, RSPREMDED,
RTRIM(RSPPLANID), RBLINGRCST, RBLDISPFEE, RBLSLSTAX, RBLPATPAY,
RBLDUEAMT, RBLWITHHLD, RBLFCOPAY, RBLPCOPAY, RBLCOPAY, RBLPRODSEL,
RBLATRTAX, RBLEXCEBFT, RBLINCENTV, RBLATRDED, RBLTOTHAMT, RBLPROFFEE,
RBLOTHPAYA, RBLCOTSRC, RTRIM(RBLCOSTTYP), RTRIM(RBLPRCTYPE),
RTRIM(PRODAYSSUP),
PROQTY, INDDEDPTD, FAMDEDPTD, DEDFLAG, BFTMAXFLAG, REBILLFLAG,
OOPFLAG, RXTFLAG, REIMBURSMT, CLMORIGIN,
HLDCLMFLAG, HLDCLMDAYS, DTEINJURY
FROM
THE_FILE;

Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.