cancel
Showing results for 
Search instead for 
Did you mean: 

SQL table holding relation between items

Genius
Genius

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)

3 REPLIES

Re: SQL table holding relation between items

Genius
Genius

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.

2016-05-11_12-42-20-151.png

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

Re: SQL table holding relation between items

Pioneer
Pioneer

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

Re: SQL table holding relation between items

Genius
Genius

Thanks yuri!

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