Query to Retrieve the Compare Report Data for Projects created in App Designer

No votes yet

1> Run the Compare Reports successfully through App Designer and then run the following query to get the correct results.

SELECT DISTINCT (CASE WHEN PI.OBJECTTYPE=0 THEN 'Record'
             WHEN PI.OBJECTTYPE=1 THEN 'Index'
             WHEN PI.OBJECTTYPE=2 THEN 'Field'
             WHEN PI.OBJECTTYPE=3 THEN 'Field Format'
             WHEN PI.OBJECTTYPE=4 THEN 'Translate'
             WHEN PI.OBJECTTYPE=5 THEN 'Page'
             WHEN PI.OBJECTTYPE=6 THEN 'Menu'
             WHEN PI.OBJECTTYPE=7 THEN 'Component'
             WHEN PI.OBJECTTYPE=8 THEN 'Record PeopleCode'
             WHEN PI.OBJECTTYPE=9 THEN 'Menu PeopleCode'
             WHEN PI.OBJECTTYPE=10 THEN 'Query'
             WHEN PI.OBJECTTYPE=11 THEN 'Tree Structures'
             WHEN PI.OBJECTTYPE=12 THEN 'Trees'
             WHEN PI.OBJECTTYPE=13 THEN 'Access Groups'
             WHEN PI.OBJECTTYPE=14 THEN 'Colors'
             WHEN PI.OBJECTTYPE=15 THEN 'Styles'
             WHEN PI.OBJECTTYPE=17 THEN 'Business Process'
             WHEN PI.OBJECTTYPE=18 THEN 'Activity'
             WHEN PI.OBJECTTYPE=19 THEN 'Roles'
             WHEN PI.OBJECTTYPE=20 THEN 'Process Definitions'
             WHEN PI.OBJECTTYPE=21 THEN 'Server Definitions'
             WHEN PI.OBJECTTYPE=22 THEN 'Process Type Definitions'
             WHEN PI.OBJECTTYPE=23 THEN 'Job Definitions'
             WHEN PI.OBJECTTYPE=24 THEN 'Recurrence Definitions'
             WHEN PI.OBJECTTYPE=25 THEN 'Message Catalog Entry'
             WHEN PI.OBJECTTYPE=29 THEN 'Business Interlink'
             WHEN PI.OBJECTTYPE=30 THEN 'SQL'
             WHEN PI.OBJECTTYPE=31 THEN 'File Layout Definitions'
             WHEN PI.OBJECTTYPE=32 THEN 'Component Interface'
             WHEN PI.OBJECTTYPE=33 THEN 'Application Engine Programs'
             WHEN PI.OBJECTTYPE=34 THEN 'Application Engine Sections'
             WHEN PI.OBJECTTYPE=35 THEN 'Message Nodes'
             WHEN PI.OBJECTTYPE=36 THEN 'Message Channels'
             WHEN PI.OBJECTTYPE=37 THEN 'Messages'
             WHEN PI.OBJECTTYPE=38 THEN 'Approval Rule Sets'
             WHEN PI.OBJECTTYPE=39 THEN 'Message PeopleCode'
             WHEN PI.OBJECTTYPE=40 THEN 'Subscription PeopleCode'
             WHEN PI.OBJECTTYPE=42 THEN 'Comp. Interface PeopleCode'
             WHEN PI.OBJECTTYPE=43 THEN 'Application Engine PeopleCode'
             WHEN PI.OBJECTTYPE=44 THEN 'Page PeopleCode'
             WHEN PI.OBJECTTYPE=46 THEN 'Component PeopleCode'
             WHEN PI.OBJECTTYPE=47 THEN 'Component Record PeopleCode'
             WHEN PI.OBJECTTYPE=48 THEN 'Component Rec Fld PeopleCode'
             WHEN PI.OBJECTTYPE=49 THEN 'Images'
             WHEN PI.OBJECTTYPE=50 THEN 'Style Sheets'
             WHEN PI.OBJECTTYPE=51 THEN 'HTML'
             WHEN PI.OBJECTTYPE=53 THEN 'Permission Lists'
             WHEN PI.OBJECTTYPE=54 THEN 'Portal Registry Definitions'
             WHEN PI.OBJECTTYPE=55 THEN 'Portal Registry Structures'
             WHEN PI.OBJECTTYPE=56 THEN 'URL Definitions'
             WHEN PI.OBJECTTYPE=57 THEN 'Application Packages'
             WHEN PI.OBJECTTYPE=58 THEN 'Application Package PeopleCode'
             WHEN PI.OBJECTTYPE=60 THEN 'Analytic Types'
             WHEN PI.OBJECTTYPE=61 THEN 'Archive Templates'
             WHEN PI.OBJECTTYPE=62 THEN 'XSLT'
             WHEN PI.OBJECTTYPE=64 THEN 'Mobile Pages'
             WHEN PI.OBJECTTYPE=65 THEN 'Relationships'
             WHEN PI.OBJECTTYPE=66 THEN 'CI Property PeopleCode'
             WHEN PI.OBJECTTYPE=67 THEN 'Optimization Models'
             WHEN PI.OBJECTTYPE=68 THEN 'File References'
             WHEN PI.OBJECTTYPE=70 THEN 'Archive Object Definitions'
             WHEN PI.OBJECTTYPE=71 THEN 'Archive Templates (Type 2)'
             WHEN PI.OBJECTTYPE=72 THEN 'Diagnostic Plug-Ins'
             WHEN PI.OBJECTTYPE=73 THEN 'Analytic Models'
             WHEN PI.OBJECTTYPE=76 THEN 'WSRP Remote Producers'
             WHEN PI.OBJECTTYPE=77 THEN 'WSRP Remote Portlets'
             WHEN PI.OBJECTTYPE=78 THEN 'WSRP Cloned Portlet Handles'
             ELSE 'Unknown'
        END) AS "Object Type"
         , PI.OBJECTVALUE1
                 , PI.OBJECTVALUE2
                 , (CASE WHEN PI.SOURCESTATUS = 0 THEN 'Unknown'
                     WHEN PI.SOURCESTATUS = 1 THEN 'Absent'
                     WHEN PI.SOURCESTATUS = 2 THEN 'Changed'
                     WHEN PI.SOURCESTATUS = 3 THEN 'Unchanged'
                     WHEN PI.SOURCESTATUS = 4 THEN '*Changed'
                     WHEN PI.SOURCESTATUS = 5 THEN '*Unchanged'
                     WHEN PI.SOURCESTATUS = 6 THEN 'Same' ELSE 'Invalid Value ' END) AS "Source Status"                 ,(CASE WHEN PI.TARGETSTATUS = 0 THEN 'Unknown'
                WHEN PI.TARGETSTATUS = 1 THEN 'Absent'
                WHEN PI.TARGETSTATUS = 2 THEN 'Changed'
                WHEN PI.TARGETSTATUS = 3 THEN 'Unchanged'
                WHEN PI.TARGETSTATUS = 4 THEN '*Changed'
                WHEN PI.TARGETSTATUS = 5 THEN '*Unchanged'
                WHEN PI.TARGETSTATUS = 6 THEN 'Same'  ELSE 'Invalid Value ' END) AS "Target Status"                     , (CASE WHEN PI.UPGRADEACTION = 0 THEN 'Copy'
                    WHEN PI.UPGRADEACTION = 1 THEN 'Delete'
                    WHEN PI.UPGRADEACTION = 2 THEN 'None'
                    WHEN PI.UPGRADEACTION = 3 THEN 'CopyProp' ELSE 'Invalid Value ' END) AS "Upgrade Action"                    , (CASE WHEN PI.TAKEACTION = 0 THEN 'No'
                            WHEN PI.TAKEACTION = 1 THEN 'Yes' ELSE 'Invalid Value ' END) AS "Take Action"
FROM PSPROJECTITEM PI
WHERE PI.PROJECTNAME = 'Project_Name' -- Please enter your Project Name
--AND PI.SOURCESTATUS IN (4,5)
ORDER BY 1,2
 

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
8 + 9 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.