Я так понял, пока не приведу код, никто не ответит? Ладно, сами напросились... Под <текст запроса> скрывается это:
Код: Выделить всё
declare @hdrpsnrec binary(8)
declare @canvalnrec binary(8)
set @hdrpsnrec=0x8001000000002C71
declare @pbigint bigint
set @pbigint=convert(bigint, 0x80010000000002E5)
set @hdrpsnrec=convert(binary(8),@pbigint)
set @pbigint=convert(bigint,0x8000000000000000)
set @pbigint=convert(bigint, 0x8001000000000599)
set @canvalnrec=convert(binary(8),@pbigint)
declare @stackid int
set @stackid=0
declare @spstack table
(id int IDENTITY(0,1) primary key,
sid int,
param1 int
)
declare @tident int
declare @sptable1 table
(id int IDENTITY(0,1) primary key,
level int,
strindex varchar(255),
strindex2 varchar(255),
pslinesnrec binary(8),
pslinescdet binary(8),
hdrpscdetnrec binary(8),
katmcobozn varchar(50),
katmcname varchar(200),
pslinesced binary(8),
barkod varchar(20),
okp varchar(20),
pslinescpodr binary(8),
pslinesceh binary(8),
izgot binary(8),
izgots int,
edizm varchar(20),
edkoef real,
alt_edizm varchar(20),
vesid real,
rashnaed real,
kol real,
alt_koef real,
rashnaizd real,
sklad varchar(20),
ceh varchar(20),
izgotc varchar(20),
marshrut varchar(100),
dm varchar(20),
dl varchar(20),
shr varchar(20),
tol varchar(20),
razmer varchar(50),
tz varchar(50),
katmcorder int,
idce int,
typemainowner int,
idmainowner int,
KD varchar(20)
)
declare @level as int
set @level=0
insert @sptable1 (level, pslinescdet, katmcname, katmcobozn, barkod, okp,vesid,
edizm,
strindex,katmcorder,
idce,typemainowner,
izgot,izgots,
kol,rashnaed, edkoef)
select @level 'level', hdr_ps.f$cizd pslinescdet, katmc.f$name, katmc.f$obozn, katmc.f$barkod,katmc.f$okdp,katmc.f$massa,
case when hdr_ps.f$ced=0x8000000000000000 then kated.f$abbr else katotped.f$abbr end,
'',2,
0,2,
hdr_ps.f$cpodr,hdr_ps.f$wpodr,
hdr_ps.f$ednorm,1,
case when hdr_ps.f$ced=0x8000000000000000 then 1 else katotped.f$koef end
from t$hdr_ps hdr_ps
inner join t$katmc katmc
on hdr_ps.f$cizd=katmc.f$nrec
inner join t$kated kated
on katmc.f$ced=kated.f$nrec
left join t$katotped katotped
on hdr_ps.f$ced=katotped.f$nrec
where @hdrpsnrec=hdr_ps.f$nrec
while (((select count(*) from @sptable1 sptable1 where @level=sptable1.level)>0) and (@level<100))
begin
set @level=@level+1
insert @sptable1 ( level, strindex, pslinesnrec,
pslinescdet, katmcorder,katmcname, katmcobozn,
idce,
typemainowner,
idmainowner,
pslinescpodr, pslinesceh, izgot,izgots, pslinesced,
barkod,okp,vesid,
rashnaed,
kol)
select @level 'level',sptable1.strindex+str(sptable1.id,4)'|', ps_lines.f$nrec pslinesnrec,
ps_lines.f$cdet pslinescdet,typemc.f$order,katmc.f$name,katmc.f$obozn,
case when sptable1.katmcorder=2 then sptable1.id else sptable1.idce end,
case when (typemc.f$order=2) or (typemc.f$order=3) then typemc.f$order else sptable1.typemainowner end,
case when (typemc.f$order=2) or (typemc.f$order=3) then null else case when (sptable1.idmainowner is null) then sptable1.id else sptable1.idmainowner end end,
ps_lines.f$cpodr,ps_lines.f$canval1,
isnull(hdr_psvar.f$cpodr,hdr_ps.f$cpodr),isnull(hdr_psvar.f$wpodr,hdr_ps.f$cpodr),
ps_lines.f$ced,
katmc.f$barkod,katmc.f$okdp,katmc.f$massa,
ps_lines.f$kol,
sptable1.kol*ps_lines.f$kol
from @sptable1 sptable1
inner join t$hdr_ps hdr_ps
on 4=hdr_ps.f$ctypeizd and sptable1.pslinescdet = hdr_ps.f$cizd and 25=hdr_ps.f$kind and 1=hdr_ps.f$active
left join t$hdr_ps hdr_psvar
on 4=hdr_psvar.f$ctypeizd and sptable1.pslinescdet = hdr_psvar.f$cizd and 25=hdr_psvar.f$kind and @canvalnrec=hdr_psvar.f$canval1
inner join t$ps_lines ps_lines
on isnull(hdr_psvar.f$nrec,hdr_ps.f$nrec)=ps_lines.f$chdr
inner join t$katmc katmc
on ps_lines.f$cdet=katmc.f$nrec
inner join t$typemc typemc
on katmc.f$ctype=typemc.f$nrec
where @level-1=sptable1.level
order by typemc.f$order, katmc.f$obozn, katmc.f$name
end
update @sptable1 set strindex2=strindex+str(id,4)+'|', idmainowner=isnull(idmainowner,id)
update @Sptable1 set hdrpscdetnrec=isnull(hdr_psvar.f$nrec,hdr_ps.f$nrec),izgotc=case when isnull(hdr_psvar.f$wpodr,hdr_ps.f$wpodr)=2 then isnull(pizgot.f$kod,'') else '**' end
from @Sptable1 sptable1
inner join t$hdr_ps hdr_ps
on 4=hdr_ps.f$ctypeizd and sptable1.pslinescdet = hdr_ps.f$cizd and 25=hdr_ps.f$kind and 1=hdr_ps.f$active
left join t$hdr_ps hdr_psvar
on 4=hdr_psvar.f$ctypeizd and sptable1.pslinescdet = hdr_psvar.f$cizd and 25=hdr_psvar.f$kind and @canvalnrec=hdr_psvar.f$canval1
left join t$katpodr pizgot
on isnull(hdr_psvar.f$cpodr,hdr_ps.f$cpodr)=pizgot.f$nrec
update @Sptable1 set sklad=isnull(psklad.f$kod,''),ceh=isnull(pceh.f$kod,''),izgotc=isnull(izgotc,''),marshrut=isnull(izgotc,''),
edizm=isnull(edizm,case when pslinesced=0x8000000000000000 then kated.f$abbr else katotped.f$abbr end),
edkoef=case when pslinesced=0x8000000000000000 then 1 else katotped.f$koef end,
alt_edizm=case when pslinesced=0x8000000000000000
then
case when
(select otped.f$akt
from t$katotped otped
where otped.f$ckated = kated.f$nrec and otped.f$cmcusl = katmc.f$nrec)=1
then
(select otped.f$abbr
from t$katotped otped
where otped.f$pr = 1 and otped.f$akt <> 1 and otped.f$cmcusl = katmc.f$nrec)
end
else
case when katotped.f$pr=1
then edizm
else
(select otped.f$abbr
from t$katotped otped
inner join t$katmc
on otped.f$cmcusl = t$katmc.f$nrec and otped.f$pr=1
where pslinescdet = otped.f$cmcusl and pslinesced <> otped.f$nrec)
end
end,
alt_koef=case when pslinesced=0x8000000000000000
then
case when
(select otped.f$akt
from t$katotped otped
where otped.f$ckated = kated.f$nrec and otped.f$cmcusl = katmc.f$nrec)=1
then
(select otped.f$koef
from t$katotped otped
where otped.f$pr = 1 and otped.f$akt <> 1 and otped.f$cmcusl = katmc.f$nrec)
end
else
case when katotped.f$pr=1
then 1
else
(select otped.f$koef
from t$katotped otped
inner join t$katmc
on otped.f$cmcusl = t$katmc.f$nrec and otped.f$pr=1
where pslinescdet = otped.f$cmcusl and pslinesced <> otped.f$nrec)
end
end,
dm=case when paramvoldlsr.f$valempty#8#=1 then convert(varchar(20),convert(float,paramvoldlsr.f$value#8#)) else '' end,
dl=case when paramvoldlsr.f$valempty#9#=1 then convert(varchar(20),convert(float,paramvoldlsr.f$value#9#)) else '' end,
shr=case when paramvoldlsr.f$valempty#10#=1 then convert(varchar(20),convert(float,paramvoldlsr.f$value#10#)) else '' end,
tol=case when paramvoltol.f$valempty#1#=1 then convert(varchar(20),convert(float,paramvoltol.f$value#1#)) else '' end,
KD=case when paramvolKD.f$valempty#5#=1 then convert(varchar(20),convert(float,paramvolKD.f$value#5#)) else '' end,
tz=isnull(spkau.f$code,'')
from @Sptable1 sptable1
inner join t$katmc katmc
on Sptable1.pslinescdet=katmc.f$nrec
left join t$kated kated
on katmc.f$ced=kated.f$nrec
left join t$katotped katotped
on sptable1.pslinesced=katotped.f$nrec
left join t$katpodr pceh
on sptable1.pslinescpodr=pceh.f$nrec
left join t$katpodr psklad
on sptable1.pslinesceh=psklad.f$nrec
left join t$paramvol paramvoldlsr
on sptable1.pslinesnrec=paramvoldlsr.f$cobject and 11053=paramvoldlsr.f$tobject and 0=paramvoldlsr.f$block
left join t$paramvol paramvoltol
on sptable1.pslinesnrec=paramvoltol.f$cobject and 11053=paramvoltol.f$tobject and 1=paramvoltol.f$block
left join t$paramvol paramvolKD
on sptable1.pslinesnrec=paramvolKD.f$cobject and 11053=paramvolKD.f$tobject and 1=paramvolKD.f$block
left join t$spkau spkau
on (paramvoldlsr.f$valempty#7#=1) and
(convert(binary(8),convert(bigint,0x8000000000000000)+convert(bigint,paramvoldlsr.f$value#7#))=spkau.f$nrec)
while (@level>0)
begin
update @sptable1 set marshrut=sptable2.marshrut+'-'+sptable1.marshrut
from @sptable1 sptable1
inner join (select * from @sptable1 where @level=level and 5>katmcorder ) sptable2
on sptable1.idmainowner=sptable2.idmainowner
where @level-1=sptable1.level and 5>sptable1.katmcorder
set @level=@level-1
end
insert @spstack (sid)
values(@stackid)
declare @strindex1 varchar(255)
declare @katmcorder int
declare @pslinesnrec binary (8)
declare @tmptable1 table
(i int IDENTITY(0,1) primary key,
strindex varchar(255),
id int,
idce int,
repr int
)
declare @tmptable2 table
(strindex varchar(255),
id int,
idce int
)
declare @startid int
declare @strindex2 varchar(255)
declare @idce int
while (Select count(*) from @spstack)>0 begin
Select @stackid=max(id) from @spstack
select @startid=sid from @spstack where (@stackid)=id
select @strindex2=strindex2,@idce=idce from @sptable1 where @startid=id
insert @tmptable1 (strindex,repr)
values ('Узел',1)
insert @tmptable1 (strindex,id,idce,repr)
values (@strindex2,@startid,@idce,1)
insert @tmptable1 (strindex,id,idce,repr)
select strindex2,id,idce,1 from @sptable1 where @startid=idce and 1=katmcorder
order by strindex2
insert @tmptable1 (strindex,repr)
values ('СЕ узла',2)
delete from @tmptable2
insert @tmptable2 (strindex,id,idce)
select strindex2,id,idce from @sptable1 where @Startid<>id and @startid=idce and 2=katmcorder
order by strindex2
insert @spstack (sid)
select id from @tmptable2
order by id desc
insert @tmptable1 (strindex,id,idce,repr)
select strindex,id,idce,2 from @tmptable2
insert @tmptable1 (strindex,repr)
values ('Детали собств. пр-ва',3)
select @tident=@@identity
insert @tmptable1 (strindex,id,idce,repr)
select strindex2,id,idce,3 from @sptable1 where @startid=idce and 3=typemainowner
order by strindex2
if @tident=@@identity delete from @tmptable1 where @tident=i
insert @tmptable1 (strindex,repr)
values ('Стандартные покупные изделия',7)
select @tident=@@identity
insert @tmptable1 (strindex,id,idce,repr)
select strindex2,id,idce,4 from @sptable1 where @startid=idce and 7=katmcorder and 2=typemainowner
order by strindex2
if @tident=@@identity delete from @tmptable1 where @tident=i
insert @tmptable1 (strindex,repr)
values ('Покупные материалы',6)
select @tident=@@identity
insert @tmptable1 (strindex,id,idce,repr)
select strindex2,id,idce,4 from @sptable1 where @startid=idce and 6=katmcorder and 2=typemainowner
order by strindex2
if @tident=@@identity delete from @tmptable1 where @tident=i
insert @tmptable1 (strindex,repr)
values ('Покупные комплектующие',5)
select @tident=@@identity
insert @tmptable1 (strindex,id,idce,repr)
select strindex2,id,idce,4 from @sptable1 where @startid=idce and 5=katmcorder and 2=typemainowner
order by strindex2
if @tident=@@identity delete from @tmptable1 where @tident=i
insert @tmptable1 (strindex,repr)
values ('-Конец узла-',1)
delete from @spstack where (@stackid)=id
end
select tmptable1.strindex,tmptable1.repr,
isnull(sptable1.katmcobozn,'<<'+tmptable1.strindex+'>>') 'katmcobozn',
case when sptable1.katmcobozn<>'' then REPLACE(replace(sptable1.katmcname, sptable1.katmcobozn,''), ' ', ' ') else sptable1.katmcname end 'katmcname',
sptable1.barkod,sptable1.okp,sptable1.edizm, sptable1.razmer, sptable1.KD, sptable1.alt_edizm 'alted',
case when sptable1.alt_edizm<>'' then sptable1.edkoef/sptable1.alt_koef else 0 end 'altkoef',
sptable1.vesid,sptable1.rashnaed,sptable1.rashnaizd,sptable1.kol,sptable1.sklad,sptable1.ceh,sptable1.izgotc,sptable1.marshrut,
sptable1.tz, sptable1.dl,sptable1.shr,sptable1.tol,sptable1.dm,
sptable1.katmcorder,sptable1.idce,sptable1.typemainowner,sptable1.idmainowner from @tmptable1 tmptable1
left join @sptable1 sptable1
on tmptable1.id=sptable1.id