Код: Выделить всё
sqlAddStr(stmt_str,',case when katnotes.name = ? then 0 else 1 end as col_note'); Код: Выделить всё
sqlAddStr(stmt_str,',case when katnotes.name = ? or katnotes.name = ? then 0 else 1 end as col_note');
Код: Выделить всё
sqlAddStr(stmt_str,',case when (katnotes.name = ? or katnotes.name = ?) then 0 else 1 end as col_note');
вот рабочий sql запрос который дает мне нужный результат .. вот блин как это впихнуть?? а если cast не держит...а походу и многое другоеcruger писал(а):Применим. Для сложных задач и делался. Без колбэков, ессно, так и не будет применим.
Код: Выделить всё
select
t$fpco.f$ABBR as namefpco
,t$katstroy.f$name as nreckatstoy
from(
select distinct
t$dogovor.f$nrec as nrecdogovor
,t$dogovor.f$tidk as tidkdogovor
,t$dogovor.f$nodoc as nodocdogovor
,(select top 1 t$SpecMTR.f$cObj from t$spdocs inner join t$SpecMTR on t$spdocs.f$nRec=t$SpecMTR.f$cSpec and 1723=t$SpecMTR.f$COTABLE
and t$SpecMTR.f$CSALDTUNE=0x8001000000000001 where t$spdocs.f$cdoc = t$dogovor.f$nrec and t$spdocs.f$tidk in (400,401)) as nreckatstoy
from t$dogovor )as table01
left outer join t$attrdog on nrecdogovor=t$attrdog.f$cdogovor
inner join t$fpco on t$attrdog.f$cpodr = t$fpco.f$nrec
inner join t$katstroy on nreckatstoy=t$katstroy.f$nrec and
nreckatstoy in
(select
nreckatstoy as nreckatstoy
from
(select
nreckatstoy as nreckatstoy
,case when (t$katnotes.f$name = 'выполнен' or t$katnotes.f$name = 'расторгнутый' or t$katnotes.f$name = 'закрыт') then 0 else 1 end as col_note
,case when (select min(t$statlog.f$doper) as min_date from t$statlog where t$statlog.f$cdoc=nrecdogovor and t$statlog.f$doctype=tidkdogovor and t$statlog.f$cnewnote = nrecnote)>=131662608
and (select min(t$statlog.f$doper) as min_date from t$statlog where t$statlog.f$cdoc=nrecdogovor and t$statlog.f$doctype=tidkdogovor and t$statlog.f$cnewnote = nrecnote)<=131663122
then 1 else 0 end as col_date
from
(select distinct t$dogovor.f$nrec as nrecdogovor,t$dogovor.f$tidk as tidkdogovor,t$dogovor.f$cnote as nrecnote,
(select top 1 t$SpecMTR.f$cObj from t$spdocs inner join t$SpecMTR on t$spdocs.f$nRec=t$SpecMTR.f$cSpec
and 1723=t$SpecMTR.f$COTABLE and t$SpecMTR.f$CSALDTUNE=0x8001000000000001 where t$spdocs.f$cdoc = t$dogovor.f$nrec and t$spdocs.f$tidk in (400,401)) as nreckatstoy
from t$dogovor)as table02
inner join t$katnotes on nrecnote=t$katnotes.f$nrec
left outer join t$attrdog on nrecdogovor=t$attrdog.f$cdogovor
where nreckatstoy is not null) as table03
Group by nreckatstoy HAVING sum(col_note) =0 and sum(col_date)<>0)
group by t$katstroy.f$name,t$fpco.f$ABBR order by t$katstroy.f$name,t$fpco.f$ABBR