SQL SAMPLE COLLECTION
Query all systems in the model:
Select x3.ItemName
from JSystem x1
Join JNamedItem x3 on x3.oid = x1.oid
Query piping component in the model:
SELECT
d.ItemName as Areasytem,
c.ItemName as Linenumber ,
Pipepart.ItemName as Pipepart
from jpipelinesystem Pipelinenumber
join XSystemHierarchy b on Pipelinenumber.Oid=b.OidDestination
join JNamedItem c on c.Oid=Pipelinenumber.Oid
join JNamedItem d on d.Oid=b.OidOrigin
join JNamedItem Pipeline on Pipelinenumber.oid=Pipeline.oid
join XSystemHierarchy a1 on Pipelinenumber.oid=a1.OidOrigin
join JRtePipeRun a2 on a1.OidDestination=a2.oid
join XownsParts a3 on a2.Oid=a3.OidOrigin
join JNamedItem Pipepart on a3.OidDestination=Pipepart.Oid
Query gasket information in the model:
SELECT
Pipepart.Itemname as Pipepartname,
--Boltpart.ShortMaterialDescription As BoltDescription
gasketpart.ShortMaterialDescription as GasketDescription,
Pipeline.ItemName as PipeLinename,
d.ItemName as Areasytem
from jpipelinesystem Pipelinenumber
join XSystemHierarchy b on Pipelinenumber.Oid=b.OidDestination
join JNamedItem c on c.Oid=Pipelinenumber.Oid
join JNamedItem d on d.Oid=b.OidOrigin
inner join JNamedItem Pipeline on Pipelinenumber.oid=Pipeline.oid
inner join XSystemHierarchy a1 on Pipelinenumber.oid=a1.OidOrigin
inner join JRtePipeRun a2 on a1.OidDestination=a2.oid
inner join XownsParts a3 on a2.Oid=a3.OidOrigin
inner join JNamedItem Pipepart on a3.OidDestination=Pipepart.Oid
inner join XOwnsImpliedItems a4 on Pipepart.Oid=a4.OidOrigin
inner join XImpliedMatingParts a5 on a4.OidDestination=a5.OidOrigin
--join JBolt Boltpart on a5.OidDestination=Boltpart.Oid
join JGasket gasketpart on a5.OidDestination=gasketpart.Oid
--Order by Areasytem, PipeLinename
Order by Pipepartname, GasketDescription
Query bolt information in the model:
SELECT
Pipepart.Itemname as Pipepartname,
Boltpart.ShortMaterialDescription As BoltDescription,
--gasketpart.ShortMaterialDescription as GasketDescription,
Pipeline.ItemName as PipeLinename,
d.ItemName as Areasytem
from jpipelinesystem Pipelinenumber
join XSystemHierarchy b on Pipelinenumber.Oid=b.OidDestination
join JNamedItem c on c.Oid=Pipelinenumber.Oid
join JNamedItem d on d.Oid=b.OidOrigin
inner join JNamedItem Pipeline on Pipelinenumber.oid=Pipeline.oid
inner join XSystemHierarchy a1 on Pipelinenumber.oid=a1.OidOrigin
inner join JRtePipeRun a2 on a1.OidDestination=a2.oid
inner join XownsParts a3 on a2.Oid=a3.OidOrigin
inner join JNamedItem Pipepart on a3.OidDestination=Pipepart.Oid
inner join XOwnsImpliedItems a4 on Pipepart.Oid=a4.OidOrigin
inner join XImpliedMatingParts a5 on a4.OidDestination=a5.OidOrigin
join JBolt Boltpart on a5.OidDestination=Boltpart.Oid
--join JGasket gasketpart on a5.OidDestination=gasketpart.Oid
--Order by Areasytem, PipeLinename
Order by Pipepartname, BoltDescription
List the child-parent system names:
Select x2.ItemName as Child, x4.ItemName as Parent
from JSystemChild x1
Join JSystem x5 on x5.oid = x1.oid
Join JNamedItem x2 on x2.oid = x1.oid
Join XSystemHierarchy x3 on x3.oidDestination = x2.oid
Join JNamedItem x4 on x4.oid = x3.oidOrigin
Query piping parts where pipelines are in piping system
Select
x3.ItemName as PipingSystem, x7.ItemName as Pipeline, x6.ItemName as Part
from JSystemChild x1 -- Pipeline system
Join JNamedItem x7 on x7.oid = x1.oid -- get pipeline name
Join XSystemHierarchy x2 on x2.oiddestination = x1.oid -- get the parent
Join JNamedItem x3 on x3.oid = x2.oidOrigin -- get parent name
Join JPipingSystem x4 on x4.oid = x2.oidOrigin -- parent = pipsys
Join YPipelineToPipingParts x5 on x5.oiddestination = x1.oid -- get parts
Join JNamedItem x6 on x6.oid = x5.oidOrigin -- get part name
Query all allowable specs per Piping Systems
select
x5.SpecName as AllowableSpec,
x6.ItemName as PipingSystem
from JDSpec x1 -- get all spec in model
join JDPipeSpec x5 on x5.oid = x1.oid -- get only pipe spec
-- return only modified systems using the SystemSpec relation
join XSystemsSpecs x2 on x2.oiddestination = x1.oid
join JNamedItem x6 on x2.oidOrigin = x6.oid -- get modified system name
-- where modified system is in subquery
where x2.oidOrigin in
(select x3.oid from JPipingSystem x3 -- return piping systems
join JNamedItem x4 on x4.oid = x3.oid )
order by x6.ItemName
Query all allowable specs per Piping Systems
Select
x5.SpecName as AllowableSpec,
x6.ItemName as PipingSystem
from JDSpec x1 -- get all spec in model
join JDPipeSpec x5 on x5.oid = x1.oid -- get only pipe spec
-- return only modified systems using the SystemSpec relation
join XSystemsSpecs x2 on x2.oiddestination = x1.oid
join JNamedItem x6 on x2.oidOrigin = x6.oid -- get modified system name
-- where modified system is in subquery
where x2.oidOrigin in
(select x3.oid from JPipingSystem x3 -- return piping systems
join JNamedItem x4 on x4.oid = x3.oid )
order by x6.ItemName
Query pipe length and area surface
select
x2.ItemName
, x3.Length [Lenght, M]
, x5.PipingOutsideDiameter * 1000 [Pipe OD, MM]
, (3.14 * x5.PipingOutsideDiameter * x3.Length ) as [Area, M2]
, x7.ItemName [PipeRun]
from JPartOcc x1
join JNamedItem x2 on x2.oid = x1.Oid
join JRteStockPartOccur x3 on x3.oid = x1.Oid
join XDistribPorts x4 on x4.OidOrigin = x1.Oid
join JDPipePort x5 on x5.Oid = x4.OidDestination
join XOwnsParts x6 on x6.OidDestination = x1.Oid
join JNamedItem x7 on x7.Oid = x6.OidOrigin
order by PipeRun
