metaclass | А вот кому ада и израиля?

Частично сгенерено, частично написано руками:

with 
  recalcs as (
    select LandTaxObject2s_UV.*,
           imns.MTRFT_Name imns_name,imns.MTRFT_SCode,
           categ.MTRFT_Name categ_name,
           LTObjectRecalcs.*,
  recpay_1.LTRecPay_TotalToPay recpay_1_ToPay,
  recpay_1.LTRecPay_Payed recpay_1_Payed,
  recpay_3.LTRecPay_TotalToPay recpay_3_ToPay,
  recpay_3.LTRecPay_Payed recpay_3_Payed,
  recpay_5.LTRecPay_TotalToPay recpay_5_ToPay,
  recpay_5.LTRecPay_Payed recpay_5_Payed,
  recpay_8.LTRecPay_TotalToPay recpay_8_ToPay,
  recpay_8.LTRecPay_Payed recpay_8_Payed,
  recpay_9.LTRecPay_TotalToPay recpay_9_ToPay,
  recpay_9.LTRecPay_Payed recpay_9_Payed

    from LTObjectRecalcs
     join LandTaxObject2s_UV on LndTx2_ID=LTObjRec_ObjID
     left join MetaRefTable imns on imns.MTRFT_XMLOBJ_ID=LndTx2_imns
     left join MetaRefTable categ on categ.MTRFT_Type=18 and categ.MTRFT_SCODE=LndTx2_Category
  left join LTRecalcPayments recpay_1 on 
     recpay_1.LTRECPAY_OBJID=LTObjRec_ObjID and recpay_1.LTRecPay_DocID=:prm_LandTaxDocument_REF and
     recpay_1.LTRecPay_RecTermIndex=LTObjRec_RecTermIndex and
     recpay_1.LTRecPay_PayTermID=1

  left join LTRecalcPayments recpay_3 on 
     recpay_3.LTRECPAY_OBJID=LTObjRec_ObjID and recpay_3.LTRecPay_DocID=:prm_LandTaxDocument_REF and
     recpay_3.LTRecPay_RecTermIndex=LTObjRec_RecTermIndex and
     recpay_3.LTRecPay_PayTermID=3

  left join LTRecalcPayments recpay_5 on 
     recpay_5.LTRECPAY_OBJID=LTObjRec_ObjID and recpay_5.LTRecPay_DocID=:prm_LandTaxDocument_REF and
     recpay_5.LTRecPay_RecTermIndex=LTObjRec_RecTermIndex and
     recpay_5.LTRecPay_PayTermID=5

  left join LTRecalcPayments recpay_8 on 
     recpay_8.LTRECPAY_OBJID=LTObjRec_ObjID and recpay_8.LTRecPay_DocID=:prm_LandTaxDocument_REF and
     recpay_8.LTRecPay_RecTermIndex=LTObjRec_RecTermIndex and
     recpay_8.LTRecPay_PayTermID=8

  left join LTRecalcPayments recpay_9 on 
     recpay_9.LTRECPAY_OBJID=LTObjRec_ObjID and recpay_9.LTRecPay_DocID=:prm_LandTaxDocument_REF and
     recpay_9.LTRecPay_RecTermIndex=LTObjRec_RecTermIndex and
     recpay_9.LTRecPay_PayTermID=9

    where LTObjRec_DocID=:prm_LandTaxDocument_REF and
          (LndTx2_dept=:dept_id or :dept_id=0) and
          (LndTx2_imns=:imns_id or :imns_id=0)

  ),
  imnsitogs  as (
    select LndTx2_imns,min(imns_name) imns_name,
           LndTx2_Kind ,
sum(LTOBJREC_TAXTOTAL) LTOBJREC_TAXTOTAL,
sum(LTOBJREC_FREETOTAL) LTOBJREC_FREETOTAL, 
sum(LTOBJREC_TOTALTOPAY) LTOBJREC_TOTALTOPAY, 
sum(RECPAY_1_TOPAY) RECPAY_1_TOPAY, sum(RECPAY_1_PAYED) RECPAY_1_PAYED, sum(RECPAY_3_TOPAY) RECPAY_3_TOPAY, sum(RECPAY_3_PAYED) RECPAY_3_PAYED, sum(RECPAY_5_TOPAY) RECPAY_5_TOPAY, sum(RECPAY_5_PAYED) RECPAY_5_PAYED, sum(RECPAY_8_TOPAY) RECPAY_8_TOPAY, sum(RECPAY_8_PAYED) RECPAY_8_PAYED, sum(RECPAY_9_TOPAY) RECPAY_9_TOPAY, sum(RECPAY_9_PAYED) RECPAY_9_PAYED
           from     recalcs
    group by LndTx2_Kind,LndTx2_imns
  )
select * from 
(
select 3 sk,LNDTX2_ID, LNDTX2_DEPT, LNDTX2_IMNS, LNDTX2_KIND, LNDTX2_NUMBER, LNDTX2_OBLAST, LNDTX2_RAION, LNDTX2_GOROD, LNDTX2_ACTS, LNDTX2_DESC, LNDTX2_ADDRESS, LNDTX2_CATEGORY
, LNDTX2_FUNCKIND, LNDTX2_AREA, LNDTX2_TERMBEGIN, LNDTX2_TERMEND
, IMNS_NAME, MTRFT_SCODE, CATEG_NAME
, LTOBJREC_OBJID, LTOBJREC_DOCID, LTOBJREC_RECTERMINDEX, LTOBJREC_LEASETOTAL, LTOBJREC_CADASTRRATEVAL, LTOBJREC_CADASTRRATE, LTOBJREC_CADASTRTOTAL, LTOBJREC_FUNCRATE, LTOBJREC_TAXRATE, LTOBJREC_TERMKOEF, LTOBJREC_KOEFLOCAL, LTOBJREC_TAXTOTAL
, LTOBJREC_FREEAREA, LTOBJREC_FREECADASTRTOTAL, LTOBJREC_FREETAXRATE, LTOBJREC_FREETERMBEGIN, LTOBJREC_FREETERMEND, LTOBJREC_FREETERMKOEF, LTOBJREC_FREETOTAL, LTOBJREC_TOTALTOPAY, LTOBJREC_TAXRATEUNIT
, RECPAY_1_TOPAY, RECPAY_1_PAYED, RECPAY_3_TOPAY, RECPAY_3_PAYED, RECPAY_5_TOPAY, RECPAY_5_PAYED, RECPAY_8_TOPAY, RECPAY_8_PAYED, RECPAY_9_TOPAY, RECPAY_9_PAYED
 from recalcs

 union all 
select 6 sk,LNDTX2_ID, LNDTX2_DEPT, LNDTX2_IMNS, LNDTX2_KIND, LNDTX2_NUMBER, LNDTX2_OBLAST, LNDTX2_RAION, LNDTX2_GOROD, LNDTX2_ACTS, LNDTX2_DESC, LNDTX2_ADDRESS, LNDTX2_CATEGORY
, LNDTX2_FUNCKIND, LNDTX2_AREA, LNDTX2_TERMBEGIN, LNDTX2_TERMEND
, IMNS_NAME, MTRFT_SCODE, CATEG_NAME
, LTOBJREC_OBJID, LTOBJREC_DOCID, LTOBJREC_RECTERMINDEX, LTOBJREC_LEASETOTAL, LTOBJREC_CADASTRRATEVAL, LTOBJREC_CADASTRRATE, LTOBJREC_CADASTRTOTAL, LTOBJREC_FUNCRATE, LTOBJREC_TAXRATE, LTOBJREC_TERMKOEF, LTOBJREC_KOEFLOCAL, LTOBJREC_TAXTOTAL
, LTOBJREC_FREEAREA, LTOBJREC_FREECADASTRTOTAL, LTOBJREC_FREETAXRATE, LTOBJREC_FREETERMBEGIN, LTOBJREC_FREETERMEND, LTOBJREC_FREETERMKOEF, LTOBJREC_FREETOTAL, LTOBJREC_TOTALTOPAY, LTOBJREC_TAXRATEUNIT
, RECPAY_1_TOPAY, RECPAY_1_PAYED, RECPAY_3_TOPAY, RECPAY_3_PAYED, RECPAY_5_TOPAY, RECPAY_5_PAYED, RECPAY_8_TOPAY, RECPAY_8_PAYED, RECPAY_9_TOPAY, RECPAY_9_PAYED
 from recalcs

 union all 
select 2 sk,null, null, LNDTX2_IMNS, LNDTX2_KIND, null, null, null, null, null, null, null, null
, null, null, null, null
, null, null, null
, null, null, null, null, null, null, null, null, null, null, null, null
, null, null, null, null, null, null, null, null, null
, null, null, null, null, null, null, null, null, null, null
 from imnsitogs

 union all 
select 5 sk,null, null, LNDTX2_IMNS, LNDTX2_KIND, null, null, null, null, null, null, null, null
, null, null, null, null
, null, null, null
, null, null, null, null, null, null, null, null, null, null, null, null
, null, null, null, null, null, null, null, null, null
, null, null, null, null, null, null, null, null, null, null
 from imnsitogs

 union all 
select 4 sk,null, null, LNDTX2_IMNS, LNDTX2_KIND, null, null, null, null, null, null, null, null
, null, null, null, null
, null, null, null
, null, null, null, null, null, null, null, null, null, null, null, LTOBJREC_TAXTOTAL
, null, null, null, null, null, null, LTOBJREC_FREETOTAL, LTOBJREC_TOTALTOPAY, null
, RECPAY_1_TOPAY, RECPAY_1_PAYED, RECPAY_3_TOPAY, RECPAY_3_PAYED, RECPAY_5_TOPAY, RECPAY_5_PAYED, RECPAY_8_TOPAY, RECPAY_8_PAYED, RECPAY_9_TOPAY, RECPAY_9_PAYED
 from imnsitogs

 union all 
select 7 sk,null, null, LNDTX2_IMNS, LNDTX2_KIND, null, null, null, null, null, null, null, null
, null, null, null, null
, null, null, null
, null, null, null, null, null, null, null, null, null, null, null, LTOBJREC_TAXTOTAL
, null, null, null, null, null, null, LTOBJREC_FREETOTAL, LTOBJREC_TOTALTOPAY, null
, RECPAY_1_TOPAY, RECPAY_1_PAYED, RECPAY_3_TOPAY, RECPAY_3_PAYED, RECPAY_5_TOPAY, RECPAY_5_PAYED, RECPAY_8_TOPAY, RECPAY_8_PAYED, RECPAY_9_TOPAY, RECPAY_9_PAYED
 from imnsitogs

 union all 
select 1 sk,null, null, null, LNDTX2_KIND, null, null, null, null, null, null, null, null
, null, null, null, null
, null, null, null
, null, null, null, null, null, null, null, null, null, null, null, sum(LTOBJREC_TAXTOTAL) LTOBJREC_TAXTOTAL
, null, null, null, null, null, null, sum(LTOBJREC_FREETOTAL) LTOBJREC_FREETOTAL, sum(LTOBJREC_TOTALTOPAY) LTOBJREC_TOTALTOPAY, null
, sum(RECPAY_1_TOPAY) RECPAY_1_TOPAY, sum(RECPAY_1_PAYED) RECPAY_1_PAYED, sum(RECPAY_3_TOPAY) RECPAY_3_TOPAY, sum(RECPAY_3_PAYED) RECPAY_3_PAYED, sum(RECPAY_5_TOPAY) RECPAY_5_TOPAY, sum(RECPAY_5_PAYED) RECPAY_5_PAYED, sum(RECPAY_8_TOPAY) RECPAY_8_TOPAY, sum(RECPAY_8_PAYED) RECPAY_8_PAYED, sum(RECPAY_9_TOPAY) RECPAY_9_TOPAY, sum(RECPAY_9_PAYED) RECPAY_9_PAYED
 from recalcs
 group by LndTx2_Kind

) 
order by LndTx2_Kind,LndTx2_imns,sk,LndTx2_dept,ACC_STRTOINTDEF(LndTx2_Number,0)

План запроса:

PLAN (RECALCS LANDTAXOBJECT2S_UV ACCESS_LEVELS INDEX (RDB$PRIMARY2, RDB$PRIMARY2))
PLAN (RECALCS LANDTAXOBJECT2S_UV USERIMNSES INDEX (PK_USERIMNSES, PK_USERIMNSES))
PLAN (RECALCS LANDTAXOBJECT2S_UV USERIMNSES INDEX (PK_USERIMNSES, PK_USERIMNSES))
PLAN (RECALCS LANDTAXOBJECT2S_UV ACCESS_LEVELS INDEX (RDB$PRIMARY2, RDB$PRIMARY2))
PLAN (RECALCS LANDTAXOBJECT2S_UV USERIMNSES INDEX (PK_USERIMNSES, PK_USERIMNSES))
PLAN (RECALCS LANDTAXOBJECT2S_UV USERIMNSES INDEX (PK_USERIMNSES, PK_USERIMNSES))
PLAN (IMNSITOGS RECALCS LANDTAXOBJECT2S_UV ACCESS_LEVELS INDEX (RDB$PRIMARY2, RDB$PRIMARY2))
PLAN (IMNSITOGS RECALCS LANDTAXOBJECT2S_UV USERIMNSES INDEX (PK_USERIMNSES, PK_USERIMNSES))
PLAN (IMNSITOGS RECALCS LANDTAXOBJECT2S_UV USERIMNSES INDEX (PK_USERIMNSES, PK_USERIMNSES))
PLAN (IMNSITOGS RECALCS LANDTAXOBJECT2S_UV ACCESS_LEVELS INDEX (RDB$PRIMARY2, RDB$PRIMARY2))
PLAN (IMNSITOGS RECALCS LANDTAXOBJECT2S_UV USERIMNSES INDEX (PK_USERIMNSES, PK_USERIMNSES))
PLAN (IMNSITOGS RECALCS LANDTAXOBJECT2S_UV USERIMNSES INDEX (PK_USERIMNSES, PK_USERIMNSES))
PLAN (IMNSITOGS RECALCS LANDTAXOBJECT2S_UV ACCESS_LEVELS INDEX (RDB$PRIMARY2, RDB$PRIMARY2))
PLAN (IMNSITOGS RECALCS LANDTAXOBJECT2S_UV USERIMNSES INDEX (PK_USERIMNSES, PK_USERIMNSES))
PLAN (IMNSITOGS RECALCS LANDTAXOBJECT2S_UV USERIMNSES INDEX (PK_USERIMNSES, PK_USERIMNSES))
PLAN (IMNSITOGS RECALCS LANDTAXOBJECT2S_UV ACCESS_LEVELS INDEX (RDB$PRIMARY2, RDB$PRIMARY2))
PLAN (IMNSITOGS RECALCS LANDTAXOBJECT2S_UV USERIMNSES INDEX (PK_USERIMNSES, PK_USERIMNSES))
PLAN (IMNSITOGS RECALCS LANDTAXOBJECT2S_UV USERIMNSES INDEX (PK_USERIMNSES, PK_USERIMNSES))
PLAN (RECALCS LANDTAXOBJECT2S_UV ACCESS_LEVELS INDEX (RDB$PRIMARY2, RDB$PRIMARY2))
PLAN (RECALCS LANDTAXOBJECT2S_UV USERIMNSES INDEX (PK_USERIMNSES, PK_USERIMNSES))
PLAN (RECALCS LANDTAXOBJECT2S_UV USERIMNSES INDEX (PK_USERIMNSES, PK_USERIMNSES))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (RECALCS LTOBJECTRECALCS INDEX (FK_LTOBJREC_DOCID), RECALCS LANDTAXOBJECT2S_UV LANDTAXOBJECT2S INDEX (PK_LANDTAXOBJECT2S)), RECALCS IMNS INDEX (PK_METAREFTABLE)), RECALCS CATEG INDEX (U_TYPESCODE)), RECALCS RECPAY_1 INDEX (PK_LTRECALCPAYMENTS)), RECALCS RECPAY_3 INDEX (PK_LTRECALCPAYMENTS)), RECALCS RECPAY_5 INDEX (PK_LTRECALCPAYMENTS)), RECALCS RECPAY_8 INDEX (PK_LTRECALCPAYMENTS)), RECALCS RECPAY_9 INDEX (PK_LTRECALCPAYMENTS))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (RECALCS LTOBJECTRECALCS INDEX (FK_LTOBJREC_DOCID), RECALCS LANDTAXOBJECT2S_UV LANDTAXOBJECT2S INDEX (PK_LANDTAXOBJECT2S)), RECALCS IMNS INDEX (PK_METAREFTABLE)), RECALCS CATEG INDEX (U_TYPESCODE)), RECALCS RECPAY_1 INDEX (PK_LTRECALCPAYMENTS)), RECALCS RECPAY_3 INDEX (PK_LTRECALCPAYMENTS)), RECALCS RECPAY_5 INDEX (PK_LTRECALCPAYMENTS)), RECALCS RECPAY_8 INDEX (PK_LTRECALCPAYMENTS)), RECALCS RECPAY_9 INDEX (PK_LTRECALCPAYMENTS))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (SORT (JOIN (IMNSITOGS RECALCS LTOBJECTRECALCS INDEX (FK_LTOBJREC_DOCID), IMNSITOGS RECALCS LANDTAXOBJECT2S_UV LANDTAXOBJECT2S INDEX (PK_LANDTAXOBJECT2S))), IMNSITOGS RECALCS IMNS INDEX (PK_METAREFTABLE)), IMNSITOGS RECALCS CATEG INDEX (U_TYPESCODE)), IMNSITOGS RECALCS RECPAY_1 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_3 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_5 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_8 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_9 INDEX (PK_LTRECALCPAYMENTS))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (SORT (JOIN (IMNSITOGS RECALCS LTOBJECTRECALCS INDEX (FK_LTOBJREC_DOCID), IMNSITOGS RECALCS LANDTAXOBJECT2S_UV LANDTAXOBJECT2S INDEX (PK_LANDTAXOBJECT2S))), IMNSITOGS RECALCS IMNS INDEX (PK_METAREFTABLE)), IMNSITOGS RECALCS CATEG INDEX (U_TYPESCODE)), IMNSITOGS RECALCS RECPAY_1 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_3 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_5 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_8 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_9 INDEX (PK_LTRECALCPAYMENTS))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (SORT (JOIN (IMNSITOGS RECALCS LTOBJECTRECALCS INDEX (FK_LTOBJREC_DOCID), IMNSITOGS RECALCS LANDTAXOBJECT2S_UV LANDTAXOBJECT2S INDEX (PK_LANDTAXOBJECT2S))), IMNSITOGS RECALCS IMNS INDEX (PK_METAREFTABLE)), IMNSITOGS RECALCS CATEG INDEX (U_TYPESCODE)), IMNSITOGS RECALCS RECPAY_1 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_3 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_5 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_8 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_9 INDEX (PK_LTRECALCPAYMENTS))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (SORT (JOIN (IMNSITOGS RECALCS LTOBJECTRECALCS INDEX (FK_LTOBJREC_DOCID), IMNSITOGS RECALCS LANDTAXOBJECT2S_UV LANDTAXOBJECT2S INDEX (PK_LANDTAXOBJECT2S))), IMNSITOGS RECALCS IMNS INDEX (PK_METAREFTABLE)), IMNSITOGS RECALCS CATEG INDEX (U_TYPESCODE)), IMNSITOGS RECALCS RECPAY_1 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_3 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_5 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_8 INDEX (PK_LTRECALCPAYMENTS)), IMNSITOGS RECALCS RECPAY_9 INDEX (PK_LTRECALCPAYMENTS))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (SORT (JOIN (RECALCS LTOBJECTRECALCS INDEX (FK_LTOBJREC_DOCID), RECALCS LANDTAXOBJECT2S_UV LANDTAXOBJECT2S INDEX (PK_LANDTAXOBJECT2S))), RECALCS IMNS INDEX (PK_METAREFTABLE)), RECALCS CATEG INDEX (U_TYPESCODE)), RECALCS RECPAY_1 INDEX (PK_LTRECALCPAYMENTS)), RECALCS RECPAY_3 INDEX (PK_LTRECALCPAYMENTS)), RECALCS RECPAY_5 INDEX (PK_LTRECALCPAYMENTS)), RECALCS RECPAY_8 INDEX (PK_LTRECALCPAYMENTS)), RECALCS RECPAY_9 INDEX (PK_LTRECALCPAYMENTS))

Если бы у меня была возможность впихнуть между сервером и клиентом расчетный алгоритм на F#, это все сократилось бы раз в 10. А если была возможность выдавать генератору печатных отчетов не реляционные таблицы - то кроме этого и еще раза в два уменьшилось бы.