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

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

          SQL SELECT
                    t1.cmc as cmc,
                    katpodr.nrec as cpodr,
                    COALESCE(mol.nrec, #comp(0)) as cmol,
                    COALESCE(party.nrec, #comp(0)) as cparty,
                    t1.kol as kol
              from(
                    select s.cmc,s.cmol,s.cparty,s.cpodr,s.kolp,s.kolr,s.kol,s.srprice,s.dsaldo,
                           max(s.dsaldo) OVER(partition by s.cmc, s.cmol, s.cparty, s.cpodr) as maxdate
                    from saldomc s inner join katpodr podr on s.cpodr=podr.nrec
                      where s.dsaldo<=#date(31,12,2100) and s.sp=0 //and s.cmc=#comp(0001000000000008h)
                  ) t1
                                inner join katpodr on t1.cpodr=katpodr.nrec
                left join katmol mol on t1.cmol=mol.nrec
                left join katparty party on t1.cparty=party.nrec
                                where dsaldo = maxdate and (t1.kolp-t1.kolr)<>0
               ;

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

.create view t1
as SQL SELECT
					   t1.cmc as cmc,
					   t1.name,
					   katpodr.nrec as cpodr,
					   katpodr.name,
					   COALESCE(mol.nrec, #comp(0)) as cmol,
					   COALESCE(party.nrec, #comp(0)) as cparty,
					   t1.kol
				 from(
					   select s.cmc,mc.name, s.cmol,s.cparty,s.cpodr,s.kolp,s.kolr,s.kol,s.srprice,s.dsaldo,
							  max(s.dsaldo) OVER(partition by s.cmc, s.cmol, s.cparty, s.cpodr) as maxdate
					   from katmc mc, NORMZAPAS nz, NORMZAPASSPEC nzs, saldomc s inner join katpodr podr on s.cpodr=podr.nrec
					   where s.dsaldo<=#date(31,12,2100)
					   and NZ.ATL_NREC = NZS.CNORM
					   and NZS.CMC = s.cmc
					   and s.CPODR = NZ.CPODRAZD
					   and s.cmc = mc.nrec
					   and s.cpodr <> ('0000000000000000h')
					   //and s.sp=0
					  ) t1
								   inner join katpodr on t1.cpodr=katpodr.nrec
				   left join katmol mol on t1.cmol=mol.nrec
				   left join katparty party on t1.cparty=party.nrec
								   where dsaldo = maxdate and (t1.kolp-t1.kolr)<>0;

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

.FORM 'Ведомость наличия норм страхового запаса'
.ard
.var
day1,month1,year1:integer;
day2,month2,year2:integer;
BegDate,EndDate:date;
stmt, astmt : longint;
.endvar
.begin
RunDIALOG('C_COMMON::GETINTERVAL',BegDate, EndDate);
day1:=day(BegDate);
month1:=month(BegDate);
year1:=Year(BegDate);
day2:=day(EndDate);
month2:=month(EndDate);
year2:=Year(EndDate);
end.
.var
T_kmc:longint;
T_mc, T_rtnpa, T_khar, T_ed, T_rhar:string;
T_kol:double;
sch , x , xlsi, ks : integer ;
.endvar

	table struct tnormost
		(
			mcname : string, 
			podrname : string, 
			kolost : double, 
			kolnorm : double
		)
	with index (n = mcname);

.create view t1
as SELECT *
from tnormost;


.begin
			stmt := sqlAllocStmt;
			//Создаем ВТ
			sqlDropTmpTable('tnormost');
!		  Message('должна удалиться',0)
!		  sqlDropTmpTable(UpCase ( UserName ) +'.MyData');
!		  Message('а теперь?',0)
			sqlCreateTmpTable('Table tnormost (mcname : string, podrname : string, kolost : double, kolnorm : double);', ctmNormal);
		  //Заполняем ВТ
!		sqlExecStmt(stmt,'select basedoc.nrec, basedoc.ddoc, basedoc.nodoc from basedoc inner join ttndoc on (BaseDoc.Nrec = TTNDOC.CDOC) and ttndoc.wtable=1102 inner join attrval on ttndoc.nrec = attrval.CREC inner join attrnam on attrval.cattrnam = attrnam.nrec and attrnam.nrec = comp(''281474976711117'')');
			sqlExecStmt(stmt,'SELECT t1.name, katpodr.name, t1.kol, t1.normkol from( select s.cmc,mc.name, s.cmol,s.cparty,s.cpodr,s.kolp,s.kolr,s.kol,nzs.kol as normkol, s.srprice,s.dsaldo, max(s.dsaldo) OVER(partition by s.cmc, s.cmol, s.cparty, s.cpodr) as maxdate from katmc mc, NORMZAPAS nz, NORMZAPASSPEC nzs, saldomc s inner join katpodr podr on s.cpodr=podr.nrecwhere s.dsaldo<=#date(31,12,2100) and NZ.ATL_NREC = NZS.CNORM and NZS.CMC = s.cmc and s.CPODR = NZ.CPODRAZD and s.cmc = mc.nrec and s.cpodr <> (''0000000000000000h'')) t1 inner join katpodr on t1.cpodr=katpodr.nrec left join katmol mol on t1.cmol=mol.nrec left join katparty party on t1.cparty=party.nrec where dsaldo = maxdate and (t1.kolp-t1.kolr)<>0;');
		  //Читаем ВТ
!		  sqlExecStmt(stmt,'select F from MyData');
			sqlFetchInto(stmt, tntnormost);
			sqlFreeStmt(stmt);

	 T_kmc:=Tblinitnew(12,1);
	 T_mc:=Tblnewfield(T_kmc,12);
     Tblendkey(t_Kmc);
	 T_rtnpa:=Tblnewfield(T_kmc,12);
     T_khar:=Tblnewfield(T_kmc,12);
     T_ed:=Tblnewfield(T_kmc,12);
     T_kol:=Tblnewfield(T_kmc,12);
     T_rhar:=Tblnewfield(T_kmc,12);

	 TblSetDuplicate(T_kmc,true);
	 TblClearbuffer(T_kmc);
 end.