This discussion is archived
1 Reply Latest reply: Sep 17, 2013 7:31 AM by Ron M RSS

How to Query the Facility Name on a Formulation Spec?

YokoV Newbie
Currently Being Moderated

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 Pro
    Currently Being Moderated

    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';

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points