2 Replies Latest reply: Oct 1, 2012 10:06 AM by dmccall RSS

    BU sql from DB Schema

    dmccall
      I'm using the DB Schema for 6.1 trying to find the sql to retreive BUs that trade specs are tied to. The tool generates this sql and pulls no results. Is the sql incorrect?

      SELECT * FROM gsmBaseTradeSpec t1
      INNER JOIN specSummary t2 ON t1.pkid = t2.SpecID
      INNER JOIN commonBusinessUnit t3 ON t2.fkSpecBusinessUnit = t3.pkid

      Thanks,
      David
        • 1. Re: BU sql from DB Schema
          Ron M-Oracle
          Hi David,
          That BU relationship is no longer used - unfortunately, that isn't reflected in the DB Schema doc.
          You can access the Business Units of a specification by their Approved Usages collection.

          The below SQL demonstrates how to retrieve the BUs from the SpecSummary

          select
          ss.SpecNum, bun.name, bun.Alias, bu.ExternalID
          from
          SpecSummary ss
          inner join gsmApprovedUsage au on au.fkSpecID = ss.SpecID
          inner join gsmApprovedUsageBUJoin buj on buj.fkApprovedUsage = au.pkid
          inner join commonBusinessUnit bu on buj.fkBusinessUnit = bu.pkid
          inner join commonBusinessUnitName bun on bu.pkid = bun.fkSpecBusinessUnit

          if you don't need the BU ExternalID value, you can skip the commonBusinessUnit table altogether, for a small performance improvement, like so:

          select
          ss.SpecNum, bun.name, bun.Alias
          from
          SpecSummary ss
          inner join gsmApprovedUsage au on au.fkSpecID = ss.SpecID
          inner join gsmApprovedUsageBUJoin buj on buj.fkApprovedUsage = au.pkid
          inner join commonBusinessUnitName bun on buj.fkBusinessUnit = bun.fkSpecBusinessUnit
          • 2. Re: BU sql from DB Schema
            dmccall
            thank you!