Tuesday, October 9, 2018

AX, SQL SERVER - How find released items without assigned document of specified type

Question shows all released items without assigned documents of type PictureDB (=where PictureDB document is missing).
select a.* from
select itemid, namealias, 
( select count(*) from docuref 
  typeid = 'PictureDB' and
  reftableid = 175 and
  refrecid = a.recid
) as count_
from inventtable a
itemid like 'S-653-%' or  
itemid like 'S-697-' or 
itemid like 'S-698-'
) a
count_ = 0
order by a.itemid
Output could be:
itemid                                   namealias            count_
---------------------------------------- -------------------- -----------
S-653-000-0001                           AIDA ALBERT 792      0
S-653-000-0008                           AIDA AJA 5006024259  0
S-653-000-0009                           AIDA AVISA5006024257 0

No comments:

Post a Comment