I want to perform an SQL query that gives me a list of those items that have more than one other related item.
In my setup I will check on item Document and if there are more than one items of type S_Profile related with a specific relation.
So I wonder in which SQL table do I find the link between the Document item and the second S_Profil item and also in which table is the BMIDE relation name ? (This so I can list it readable in my output)
Solved! Go to Solution.
Do think I found it in PIMANRELATION and PIMANTYPE.
I would like to find the items that have two other items related with the same relation as show in picture below.
I have made this sql query but I can't get it to list the relations as two.
Someone who can advise? You may need to replace the relation and item type to something OOTB if you try to run the query.
Select Count(innerQ2.ItemID) as CountOfItemID ,MAX(innerQ2.ItemID) as IDwithError From ( Select distinct i.pitem_id "ItemID" From PITEMREVISION ir, PITEM i, PWORKSPACEOBJECT wso1 where ir.ritems_tagu=i.puid and ir.puid in ( Select ir2.puid From PITEMREVISION ir2, PWORKSPACEOBJECT wso2 where wso2.puid = ir2.puid and wso2.pobject_type = 'S2TC_PartRevision' and ir2.puid in ( /*Sorts out the item revisions which have a the related item*/ select rprimary_objectu /*Comes from table PIMANRELATION and gives the item revision id*/ from PIMANTYPE type1, PIMANRELATION rel1, PWORKSPACEOBJECT wso4 where type1.ptype_name='S2TC_vtl_ProfileSpec' and rel1.rrelation_typeu = type1.puid ) ) )innerQ2 Group by ItemID
If ItemRev sequence number is limited by 1 in your TC, you may try the next query
Select i.pitem_id, ir.pitem_revision_id, count(*) From PITEMREVISION ir join PITEM i on ir.ritems_tagu=i.puid join PWORKSPACEOBJECT wso2 on wso2.puid = ir.puid join pimanrelation r on r.rprimary_objectu = ir.puid join pimantype t on r.rrelation_typeu = t.puid join pitem i2 on r.rsecondary_objectu = i2.puid where wso2.pobject_type = 'ItemRevision' and t.ptype_name = 'IMAN_UG_wave_geometry' Group by i.pitem_id, ir.pitem_revision_id order by 3 desc
It gave me a push in correct direction ... I modified your sample a bit to only list the active sequence and all items with relation above 1.
Select i.pitem_id, ir.pitem_revision_id,t.ptype_name,wso2.puid,wso2.pobject_name,count(*) as NumberofRel From PITEMREVISION ir join PITEM i on ir.ritems_tagu=i.puid join PWORKSPACEOBJECT wso2 on wso2.puid = ir.puid join PIMANRELATION r on r.rprimary_objectu = ir.puid join PIMANTYPE t on r.rrelation_typeu = t.puid join PITEM i2 on r.rsecondary_objectu = i2.puid where wso2.pobject_type = 'S2TC_PartRevision' and wso2.pactive_seq='1' and t.ptype_name = 'S2TC_vtl_ProfileSpec' Group by i.pitem_id, ir.pitem_revision_id,t.ptype_name,wso2.puid,wso2.pobject_name Having Count(*)>1 order by NumberofRel desc