1 Reply Latest reply on Sep 15, 2015 2:52 PM by diego.medrano

    Multiple Select statements from non-linked tables

    Liz Christenbury

      Hello,

       

      I have a data structure that looks like this:

       

      GL_STAR - has multiple keys that link out to several other tables.

      BEGINNING_BALANCE_STAR - has a key that links out to a table that GL_STAR also links out to.

       

      I would like to incorporate a few columns from BEGINNING_BALANCE_STAR into my query, but am not sure how to do it.

       

      This is my existing query that is successfully executing:

       

      SELECT GL_STAR_FACT.TRANSACTION_AMOUNT,

      GL_STAR_FACT.TRANSACTION_TYPE,

      GL_STAR_FACT.EXPENSE_AMOUNT,

      GL_STAR_FACT.REVENUE_AMOUNT,

      GL_STAR_FACT.ENCUMBRANCE_AMOUNT,

      GL_STAR_FACT.BUDGET_AMOUNT,

      ACCOUNT_DIMENSION.ACCOUNT_DESCRIPTION,

      ACCOUNT_DIMENSION.ACCOUNT_TYPE,

      ACCOUNT_DIMENSION.ACCOUNT_KEY,

      JOURNAL_REFERENCE_DIMENSION.JOURNAL_DATE,

      JOURNAL_REFERENCE_DIMENSION.POSTED_DATE,

      DEPARTMENT_DIMENSION.DEPTID,

      CHARTFIELD_DIMENSION.FUND_CODE,

      PROJECT_DIMENSION.PROJECT_ID,

      PROJECT_DIMENSION.PROJECT_START_DATE,

      PROJECT_DIMENSION.PROJECT_END_DATE,

      PROJECT_DIMENSION.PROJECT_MANAGER_NAME

      FROM GL_STAR_FACT

      INNER JOIN ACCOUNT_DIMENSION

      ON GL_STAR_FACT.ACCOUNT_KEY = ACCOUNT_DIMENSION.ACCOUNT_KEY

      INNER JOIN JOURNAL_REFERENCE_DIMENSION

      ON GL_STAR_FACT.JOURNAL_REFERENCE_KEY = JOURNAL_REFERENCE_DIMENSION.JOURNAL_REFERENCE_KEY

      INNER JOIN PROJECT_DIMENSION

      ON GL_STAR_FACT.PROJECT_KEY = PROJECT_DIMENSION.PROJECT_KEY

      INNER JOIN DEPARTMENT_DIMENSION

      ON GL_STAR_FACT.DEPARTMENT_KEY = DEPARTMENT_DIMENSION.DEPARTMENT_KEY

      INNER JOIN CHARTFIELD_DIMENSION

      ON GL_STAR_FACT.CHARTFIELD_KEY = CHARTFIELD_DIMENSION.CHARTFIELD_KEY

      WHERE JOURNAL_REFERENCE_DIMENSION.BUSINESS_UNIT = 'UWMSN'

      AND PROJECT_DIMENSION.FISCAL_YEAR = '2015'

      AND PROJECT_DIMENSION.PROJECT_ID IN ('PRJ81EE','PRJ94HI','PRJ74GJ','PRJ81EA','PRJ94HE','PRJ55LX','PRJ74GI','PRJ81DZ','PRJ94HD','PRJ81DY','PRJ94HC','1362094','PRJ74GG','PRJ81DX','PRJ94HB','PRJ74GF','PRJ81DW','PRJ94HA','1362547','PRJ74GE','PRJ81DV','PRJ94GZ','PRJ74GK','PRJ81EB','PRJ94HF','1362544','PRJ81EF','1362095','PRJ74GL','PRJ81EC','PRJ94HG')

      ORDER BY JOURNAL_REFERENCE_DIMENSION.JOURNAL_DATE

       

       

      And I would like to add in some logic saying something like this:

       

      SELECT *

      FROM BEGINNING_BALANCE_STAR

      WHERE FISCAL_YEAR = '2015'

       

      I would also like to link up the ACCOUNT_DIMENSION table with the BEGINNING_BALANCE_STAR table through the ACCOUNT_KEY primary key.

       

      How would I go about doing this?