Цитата:
Сообщение от
fed
Я бы пошел по такому пути:
кстати, ты просто переформулировал мой второй пример наивной реализации с isnull )))
Код:
with trans as (
SELECT
isnull(accTable.SUMACCOUNT, isnull(accGroup.SumAccount, accAll.SumAccount)) as pSumAccount,
tr.*
from custtrans as tr
join custtable as tab on (tab.DATAAREAID = tr.DATAAREAID and tab.accountnum = tr.accountnum)
left join CUSTLEDGERACCOUNTS as accTable
on (accTable.DATAAREAID = tr.DATAAREAID
and accTable.POSTINGPROFILE = tr.POSTINGPROFILE
and accTable.AccountCode = 0 and accTable.NUM = tab.ACCOUNTNUM)
left join CUSTLEDGERACCOUNTS as accGroup
on (accTable.DATAAREAID = tr.DATAAREAID
and accGroup.POSTINGPROFILE = tr.POSTINGPROFILE
and accGroup.AccountCode = 1 and accGroup.NUM = tab.CUSTGROUP)
left join CUSTLEDGERACCOUNTS as accAll
on (accAll.DATAAREAID = tr.DATAAREAID
and accAll.POSTINGPROFILE = tr.POSTINGPROFILE
and accAll.AccountCode = 2)
)
select *
from trans
--where trans.DATAAREAID in ('dmr')