1 Reply Latest reply: Sep 17, 2013 2:31 PM by Ron M-Oracle RSS

    How to Query the Facility Name on a Formulation Spec?

    YokoV

      I would like to query the facility name from the scrmEntityFreeTextName table.  If I'm trying to drive my data from a formulation spec number, what table(s) do I have to join to do get from SPECSUMMARY to link to scrmEntityFreeTextName?

       

      select *

      from agile.scrmfacility f, agile.scrmEntityFreeTextName tn

      where f.pkid = tn.fkentity

        • 1. Re: How to Query the Facility Name on a Formulation Spec?
          Ron M-Oracle

          You can use the DatabaseAndObjectSchema tool, located in the Extensibility Pack. It is an HTML document that helps you navigate the object or data schema, and can provide the basic SQL statement needed.

          Here is the basic SQL statement it provided:

           

          SELECT * FROM formulationSpecification t1 
          INNER JOIN DWBSpecificationRoots t2 ON t1.pkid = t2.fkOwner 
          INNER JOIN dwbSpecifications t3 ON t2.pkid = t3.fkWorkingVersionParent 
          INNER JOIN gsmFormulationFacilityJoin t4 ON t3.pkid = t4.fkSpecID 
          INNER JOIN scrmFacility t5 ON t4.fkFacility = t5.pkid 
          INNER JOIN scrmEntityFreeTextName t6 ON t5.pkid = t6.fkEntity 
          where t1.pkid='581672ba6d3a-f37a-409b-a140-d4cd6eab5606' -- this uses the spec pkid
          

           

          To use specNumber instead, you join against the specSummary, then use its SpecID column for the join

           

          SELECT t6.name, * 
          FROM 
          --formulationSpecification t1 
          SpecSummary s 
          INNER JOIN DWBSpecificationRoots t2 ON s.SpecID = t2.fkOwner 
          INNER JOIN dwbSpecifications t3 ON t2.pkid = t3.fkWorkingVersionParent 
          INNER JOIN gsmFormulationFacilityJoin t4 ON t3.pkid = t4.fkSpecID 
          INNER JOIN scrmFacility t5 ON t4.fkFacility = t5.pkid 
          INNER JOIN scrmEntityFreeTextName t6 ON t5.pkid = t6.fkEntity 
          where s.SpecNumber = '5098024-001';