'Query to search items with image

I am looking for a query to look for items that have a image.

(status != 'OBSOLETE' and itemsetid = 'ITEMSET1') and (exists (select 1 from dbo.inventory where ((location = 'SPARES')) and (itemnum=item.itemnum and itemsetid = item.itemsetid))) and (itemnum in (select imglib.refobjectid from IMGLIB where refobject='ITEM'))

everything works perfectly with the exeption when adding:

itemnum in (select imglib.refobjectid from IMGLIB where refobject='ITEM')

I can't reach the db so i can't check the contents of the IMGLIB table but AFAIK this should work? refobjectid is the stored itemnum?

What am i missing?

maximo


Solution 1:[1]

When looking at Maximo version 7.6.0.3 Entity Relationship Document (ERD):

(part of) MAXIMO INCOMING RELATIONSHIPS for IMGLIB

Name source Remarks
IMGLIB ITEM Relationship to the IMGLIB table, used to find the image for a given item. (imglib.refobject='ITEM' and imglib.refobjectid=:item$ITEM.ITEMID). The resulting set will contain zero or one object.

I think you need ITEMID, and not ITEMNUM

(But I do not have knowledge of MAXIMO ... )

Solution 2:[2]

Your query should look something like this:

   itemid in (
      select refobjectid from imglib where refobject='ITEM'
   )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Luuk
Solution 2 John C

Related Questions