-- #New: 22.10.2021 /J.Fes SQL ideas find for 3D Master 2D Drawing
-- #Doc: Name: ObjectAttr_01_Ideas_for3D_find_2D_Drawing.sql
-- #Doc: If 3D has multibel 2D DRW then n line (2..3..) are created seperated by CRLF
-- select listagg(Ir2D, CHR(13)||CHR(10)) within group (order by Ir2D) As Ir2D

--Declare @para_IrPuid nvarchar(32) = 'VOONBu681x$SqC'
-- set @para_IrPuid = 'HuFNwXsE1x$SqC'
-- set @para_IrPuid = 'JVLNBfc11x$SqC' -- obs003-716-140139-318/B_001
-- set @para_IrPuid = '46INBfc11x$SqC' --  716-140139-318/B

select String_AGG(Ir2D, ';') within group (order by Ir2D) As Ir2DList
 from (
 select
	  -- '3D-to-2D' Query
	   i2D.pitem_id + '/' + ir2D.pitem_revision_id as Ir2D
       FROM
        dbo.PITEM i2D
        LEFT JOIN dbo.PITEMREVISION ir2D        on (i2D.puid=ir2D.ritems_tagu)
        LEFT JOIN dbo.PIMANRELATION irel2D      on (ir2D.puid=irel2D.rprimary_objectu)
        LEFT JOIN dbo.PWORKSPACEOBJECT wso2D    on (irel2D.rsecondary_objectu=wso2D.puid)
        LEFT JOIN dbo.PIMANRELATION irelQuery   on (wso2D.puid=irelQuery.rprimary_objectu)
        LEFT JOIN dbo.PITEMREVISION irQuery     on (irelQuery.rsecondary_objectu=irQuery.puid)
        LEFT JOIN dbo.PITEM iQuery              on (irQuery.ritems_tagu=iQuery.puid)
        WHERE
           wso2D.pobject_type='UGPART' and
           irQuery.puid = @para_IrPuid

 UNION select
	   -- '2D-to-3D' Query
	   i2D.pitem_id + '/' + ir2D.pitem_revision_id as Ir3D
       FROM
        dbo.PITEM i2D
        LEFT JOIN dbo.PITEMREVISION ir2D        on (i2D.puid=ir2D.ritems_tagu)
        LEFT JOIN dbo.PIMANRELATION irel2D      on (ir2D.puid=irel2D.rsecondary_objectu)
        LEFT JOIN dbo.PWORKSPACEOBJECT wso2D    on (irel2D.rprimary_objectu=wso2D.puid)
        LEFT JOIN dbo.PIMANRELATION irelQuery   on (wso2D.puid=irelQuery.rsecondary_objectu)
        LEFT JOIN dbo.PITEMREVISION irQuery     on (irelQuery.rprimary_objectu=irQuery.puid)
        LEFT JOIN dbo.PITEM iQuery              on (irQuery.ritems_tagu=iQuery.puid)
        WHERE
           wso2D.pobject_type='UGPART' and
           irQuery.puid = @para_IrPuid
) t

