X++:
select ct.ACCOUNTNUM, cla.ACCOUNTCODE, cla.POSTINGPROFILE, dim.DISPLAYVALUE, cla.DATAAREAID from CUSTTABLE as ct
join CUSTLEDGERACCOUNTS as cla on
ct.ACCOUNTNUM = cla.NUM and
ct.DATAAREAID = cla.DATAAREAID and
cla.ACCOUNTCODE = 0
join DIMENSIONATTRIBUTEVALUECOMBINATION as dim on
cla.SUMMARYLEDGERDIMENSION = dim.RECID
where
ct.DATAAREAID = 'usmf'
union
select ct1.ACCOUNTNUM, cla1.ACCOUNTCODE, cla1.POSTINGPROFILE, dim1.DISPLAYVALUE, cla1.DATAAREAID from CUSTTABLE as ct1
join CUSTLEDGERACCOUNTS as cla1 on
ct1.CUSTGROUP = cla1.NUM and
ct1.DATAAREAID = cla1.DATAAREAID and
cla1.ACCOUNTCODE = 1
join DIMENSIONATTRIBUTEVALUECOMBINATION as dim1 on
cla1.SUMMARYLEDGERDIMENSION = dim1.RECID
where
ct1.DATAAREAID = 'usmf'
union
select ct2.ACCOUNTNUM, cla2.ACCOUNTCODE, cla2.POSTINGPROFILE, dim2.DISPLAYVALUE, cla2.DATAAREAID from CUSTTABLE as ct2
join CUSTLEDGERACCOUNTS as cla2 on
ct2.DATAAREAID = cla2.DATAAREAID and
cla2.ACCOUNTCODE = 2
join DIMENSIONATTRIBUTEVALUECOMBINATION as dim2 on
cla2.SUMMARYLEDGERDIMENSION = dim2.RECID
where
ct2.DATAAREAID = 'usmf'
у меня такой запрос на demo выполняется мгновенно (план посмотреть не могу, прав не хватает). Думаю, что и select into будет работать так же быстро.