Галактика в вопросах и ответах

Код: Выделить всё

stmt := sqlAllocStmt;
  sqlBindParam(stmt, 1, d_sal);
  sqlBindParam(stmt, 2, d_sal);
  sqlBindParam(stmt, 3, d_sal);
  sqlAddStr(query,'select coalesce(nach.sch_subsch,ob.sch_subsch) s,coalesce(nach.nr_podr,ob_nr_podr),coalesce(nach.podr,ob.podr) p,');
  sqlAddStr(query,'coalesce(nach.nr_mc,ob_nr_mc),coalesce(nach.mc,ob.mc) m,');
  sqlAddStr(query,'coalesce(nach.kol,0),coalesce(ob.kol,0),');
  sqlAddStr(query,'coalesce(nach.sums,0),coalesce(ob.summa,0) from ');

  sqlAddStr(query,'(select saldmoun.datesal,saldmoun.dbscheto+'#39+'.'#39+'+saldmoun.subossch sch_subsch,');
  sqlAddStr(query,'saldmoun.kauos[3] nr_podr,coalesce(katpodr.name,'#39#39+') podr,saldmoun.kauos[2] nr_mc,coalesce(katmc.name,'#39#39+') mc,');
  sqlAddStr(query,'sum(case when viob=1 then saldmoun.kol else -1*saldmoun.kol end) kol,sum(case when viob=1 then saldmoun.sums else -1*saldmoun.sums end) sums ');
  sqlAddStr(query,'from saldmoun left join katmc on saldmoun.kauos[2]=katmc.nrec left join katpodr on saldmoun.kauos[3]=katpodr.nrec ');
  sqlAddStr(query,'inner join ');
  sqlAddStr(query,'(select max(saldmoun.datesal) maxdat,saldmoun.dbscheto,saldmoun.subossch,saldmoun.kauos[3],saldmoun.kauos[2] from saldmoun ');
  sqlAddStr(query,'where saldmoun.datesal<= ? ');
  sqlAddStr(query,'group by saldmoun.dbscheto,saldmoun.subossch,saldmoun.kauos[3],saldmoun.kauos[2]) posl_sal ');
  sqlAddStr(query,'on saldmoun.datesal=posl_sal.maxdat and saldmoun.dbscheto=posl_sal.dbscheto and saldmoun.subossch=posl_sal.subossch ');
  sqlAddStr(query,'and saldmoun.kauos[3]=posl_sal.kauos[3] and saldmoun.kauos[2]=posl_sal.kauos[2] ');
  sqlAddStr(query,'group by saldmoun.datesal,saldmoun.dbscheto+'#39+'.'#39+'+saldmoun.subossch,');
  sqlAddStr(query,'saldmoun.kauos[3],coalesce(katpodr.name,'#39#39+'),saldmoun.kauos[2],coalesce(katmc.name,'#39#39+')) nach ');

  sqlAddStr(query,'full outer join ');
  sqlAddStr(query,'(select oborot.datob,oborot.dbscheto+'#39+'.'#39+'+oborot.subossch sch_subsch,');
  sqlAddStr(query,'oborot.kauos[3] nr_podr,coalesce(katpodr.name,'#39#39+') podr,oborot.kauos[2] nr_mc,coalesce(katmc.name,'#39#39+') mc,');
  sqlAddStr(query,'sum(oborot.kol) kol,sum(oborot.sumob) summa ');
  sqlAddStr(query,'from oborot left join katmc on oborot.kauos[2]=katmc.nrec left join katpodr on oborot.kauos[3]=katpodr.nrec ');
  sqlAddStr(query,'where oborot.datob<= ? ');
  sqlAddStr(query,'group by oborot.datob,oborot.dbscheto+'#39+'.'#39+'+oborot.subossch,oborot.kauos[3],katpodr.name,oborot.kauos[2],katmc.name ');
  sqlAddStr(query,'union ');
  sqlAddStr(query,'select oborot.datob,oborot.krschetk+'#39+'.'#39+'+oborot.subschk sch_subsch,');
  sqlAddStr(query,'oborot.kauks[3] nr_podr,coalesce(katpodr.name,'#39#39+') podr,oborot.kauks[2] nr_mc,coalesce(katmc.name,'#39#39+') mc,');
  sqlAddStr(query,'sum(-1*oborot.kol) kol,sum(-1*oborot.sumob) summa ');
  sqlAddStr(query,'from oborot left join katmc on oborot.kauks[2]=katmc.nrec left join katpodr on oborot.kauks[3]=katpodr.nrec ');
  sqlAddStr(query,'where oborot.datob<= ? ');
  sqlAddStr(query,'group by oborot.datob,oborot.krschetk+'#39+'.'#39+'+oborot.subschk,oborot.kauks[3],katpodr.name,oborot.kauks[2],katmc.name) ob ');
  sqlAddStr(query,'on nach.sch_subsch=ob.sch_subsch and nach.podr=ob.podr and nach.mc=ob.mc ');

  sqlAddStr(query,'where coalesce(nach.datesal,#date(1,1,1900))<=ob.datob ');    

Код: Выделить всё

coalesce(nach.kol,0) nk,coalesce(ob.kol,0) ok,
coalesce(nach.sums,0) ns,coalesce(ob.summa,0) os

Код: Выделить всё

coalesce(nach.kol,0)+sum(coalesce(ob.kol,0)),
coalesce(nach.sums,0)+sum(coalesce(ob.summa,0))