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

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

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');

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

katnotes.name = ?

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

katnotes.name = ? or katnotes.name = ?

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

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 

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

case when datekatnotes>=? then
case when datekatnotes<=? then 1 else 0 end
else 0 end as col_date