SQL Query to Read Values of Outline Code

we have a project outline code project Status and type which has following values

Open

    A

    B

    C

Closed

    A

    B

    C

Proposed

    A

    B

    C

More than 10 projects has values of Closed.B. So I need to collect the projects with this outline code value.

What I did.

I opened the MPS_Outline_Codes tables and scroll down to see the value in OC_CACHED_FULL_NAME. If value is Closed.B then I copied MSP_CODE_FIELDS.CODE_FIELD_ID = 188744608) AND MSP_CODE_FIELDS.CODE_UID = 49.

I then move to MSP_Code_Fields table and look for the projects that match these both values using

select * from msp_code_fields Where CODE_Field_ID=188744608 and Code_UID=49

Then I move towards the msp_projects table and then use the view creation screen to create the following View.

 

SELECT MSP_PROJECTS.PROJ_NAME, MSP_PROJECTS.PROJ_INFO_CAL_NAME, MSP_CODE_FIELDS.CODE_FIELD_ID, MSP_OUTLINE_CODES.PROJ_ID,

MSP_OUTLINE_CODES.OC_CACHED_FULL_NAME, MSP_CODE_FIELDS.CODE_UID

FROM MSP_CODE_FIELDS INNER JOIN

MSP_PROJECTS ON MSP_CODE_FIELDS.PROJ_ID = MSP_PROJECTS.PROJ_ID INNER JOIN

MSP_OUTLINE_CODES ON MSP_CODE_FIELDS.CODE_UID = MSP_OUTLINE_CODES.CODE_UID

WHERE (MSP_CODE_FIELDS.CODE_FIELD_ID = 188744608) AND (MSP_CODE_FIELDS.CODE_UID = 49)

This Query will return the following

Project Name         Calendar    Code ID     Project ID    Field Value    CODE UID

ProjectName.Published     Standard    188744608    194        Closed.Site B    49

Regards,

Jerry

Advertisements

2 thoughts on “SQL Query to Read Values of Outline Code

  1. You need to define the the Enterprise Outline code on a specific outline code.  I dont rembmer the outline code i used in this examploy.  It was long time back so i can help in this case.
     
    Jerry

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s