SQL to find a specific string in all SQL definitions:
SELECT SQLID, CASE WHEN SQLTYPE=0 THEN 'SQL Object' WHEN SQLTYPE=1 THEN 'AE Step'
WHEN SQLTYPE=2 THEN 'SQL View' END AS SQLTYPE, SQLTEXT FROM PSSQLTEXTDEFN WHERE SQLTEXT LIKE '%<Mention search value here>%'
Below is list of SQL object type.
SQLTYPE
|
Data/Object Type
|
0
|
Stand alone SQL objects
|
1
|
Application engine step
|
2
|
SQL belongs to a view
|
SQL to find a specific string in all SQL definitions:
SELECT CASE OBJECTID1 WHEN 1 THEN 'Record' WHEN 2 THEN 'Field' WHEN 3 THEN 'Menu' WHEN 4 THEN 'Bar Name' WHEN 5 THEN 'Item Name' WHEN 9 THEN 'Page' WHEN 10 THEN 'Component' WHEN 12 THEN 'Event' WHEN 20 THEN 'DB Type' WHEN 21 THEN 'Effective Date' WHEN 39 THEN 'Market' WHEN 60 THEN 'Message' WHEN 66 THEN 'App Eng' WHEN 74 THEN 'Comp Int - CI' WHEN 77 THEN 'Section' WHEN 78 THEN 'Step' WHEN 87 THEN 'Subscription' WHEN 104 THEN 'App Package' WHEN 105 THEN 'Class' WHEN 106 THEN 'Class' WHEN 107 THEN 'Class' ELSE 'N/A' END AS OBJECTID1,OBJECTVALUE1, CASE OBJECTID2 WHEN 1 THEN 'Record' WHEN 2 THEN 'Field' WHEN 3 THEN 'Menu' WHEN 4 THEN 'Bar Name' WHEN 5 THEN 'Item Name' WHEN 9 THEN 'Page' WHEN 10 THEN 'Component' WHEN 12 THEN 'Event' WHEN 20 THEN 'DB Type' WHEN 21 THEN 'Effective Date' WHEN 39 THEN 'Market' WHEN 60 THEN 'Message' WHEN 66 THEN 'App Eng' WHEN 74 THEN 'Comp Int - CI' WHEN 77 THEN 'Section' WHEN 78 THEN 'Step' WHEN 87 THEN 'Subscription' WHEN 104 THEN 'App Package' WHEN 105 THEN 'Class' WHEN 106 THEN 'Class' WHEN 107 THEN 'Class' ELSE 'N/A' END AS OBJECTID2, OBJECTVALUE2, CASE OBJECTID3 WHEN 1 THEN 'Record' WHEN 2 THEN 'Field' WHEN 3 THEN 'Menu' WHEN 4 THEN 'Bar Name' WHEN 5 THEN 'Item Name' WHEN 9 THEN 'Page' WHEN 10 THEN 'Component' WHEN 12 THEN 'Event' WHEN 20 THEN 'DB Type' WHEN 21 THEN 'Effective Date' WHEN 39 THEN 'Market' WHEN 60 THEN 'Message' WHEN 66 THEN 'App Eng' WHEN 74 THEN 'Comp Int - CI' WHEN 77 THEN 'Section' WHEN 78 THEN 'Step' WHEN 87 THEN 'Subscription' WHEN 104 THEN 'App Package' WHEN 105 THEN 'Class' WHEN 106 THEN 'Class' WHEN 107 THEN 'Class' ELSE 'N/A' END AS OBJECTID3,OBJECTVALUE3, CASE OBJECTID4 WHEN 1 THEN 'Record' WHEN 2 THEN 'Field' WHEN 3 THEN 'Menu' WHEN 4 THEN 'Bar Name' WHEN 5 THEN 'Item Name' WHEN 9 THEN 'Page' WHEN 10 THEN 'Component' WHEN 12 THEN 'Event' WHEN 20 THEN 'DB Type' WHEN 21 THEN 'Effective Date' WHEN 39 THEN 'Market' WHEN 60 THEN 'Message' WHEN 66 THEN 'App Eng' WHEN 74 THEN 'Comp Int - CI' WHEN 77 THEN 'Section' WHEN 78 THEN 'Step' WHEN 87 THEN 'Subscription' WHEN 104 THEN 'App Package' WHEN 105 THEN 'Class' WHEN 106 THEN 'Class' WHEN 107 THEN 'Class' ELSE 'N/A' END AS OBJECTID4, OBJECTVALUE4 , CASE OBJECTID5 WHEN 1 THEN 'Record' WHEN 2 THEN 'Field' WHEN 3 THEN 'Menu' WHEN 4 THEN 'Bar Name' WHEN 5 THEN 'Item Name' WHEN 9 THEN 'Page' WHEN 10 THEN 'Component' WHEN 12 THEN 'Event' WHEN 20 THEN 'DB Type' WHEN 21 THEN 'Effective Date' WHEN 39 THEN 'Market' WHEN 60 THEN 'Message' WHEN 66 THEN 'App Eng' WHEN 74 THEN 'Comp Int - CI' WHEN 77 THEN 'Section' WHEN 78 THEN 'Step' WHEN 87 THEN 'Subscription' WHEN 104 THEN 'App Package' WHEN 105 THEN 'Class' WHEN 106 THEN 'Class' WHEN 107 THEN 'Class' ELSE 'N/A' END AS OBJECTID5,OBJECTVALUE5, CASE OBJECTID6 WHEN 1 THEN 'Record' WHEN 2 THEN 'Field' WHEN 3 THEN 'Menu' WHEN 4 THEN 'Bar Name' WHEN 5 THEN 'Item Name' WHEN 9 THEN 'Page' WHEN 10 THEN 'Component' WHEN 12 THEN 'Event' WHEN 20 THEN 'DB Type' WHEN 21 THEN 'Effective Date' WHEN 39 THEN 'Market' WHEN 60 THEN 'Message' WHEN 66 THEN 'App Eng' WHEN 74 THEN 'Comp Int - CI' WHEN 77 THEN 'Section' WHEN 78 THEN 'Step' WHEN 87 THEN 'Subscription' WHEN 104 THEN 'App Package' WHEN 105 THEN 'Class' WHEN 106 THEN 'Class' WHEN 107 THEN 'Class' ELSE 'N/A' END AS OBJECTID6,OBJECTVALUE6, CASE OBJECTID7 WHEN 1 THEN 'Record' WHEN 2 THEN 'Field' WHEN 3 THEN 'Menu' WHEN 4 THEN 'Bar Name' WHEN 5 THEN 'Item Name' WHEN 9 THEN 'Page' WHEN 10 THEN 'Component' WHEN 12 THEN 'Event' WHEN 20 THEN 'DB Type' WHEN 21 THEN 'Effective Date' WHEN 39 THEN 'Market' WHEN 60 THEN 'Message' WHEN 66 THEN 'App Eng' WHEN 74 THEN 'Comp Int - CI' WHEN 77 THEN 'Section' WHEN 78 THEN 'Step' WHEN 87 THEN 'Subscription' WHEN 104 THEN 'App Package' WHEN 105 THEN 'Class' WHEN 106 THEN 'Class' WHEN 107 THEN 'Class' ELSE 'N/A' END AS OBJECTID7,OBJECTVALUE7,PCTEXT FROM PSPCMTXT WHERE PCTEXT LIKE '%Mention search value here%';
List of OBJECTID1 field values:
ObjectID
|
Object Type
|
|
1
|
Record
|
3
|
Menu
|
9
|
Page
|
10
|
Component
|
60
|
Message
|
66
|
App Eng
|
74
|
Comp Int - CI
|
104
|
App Package
|
Please have a look and add this sql to our Retrofit approach document.
Select PNLNAME, FIELDNUM, RECNAME, FIELDNAME,CASE FIELDTYPE
WHEN 0 THEN 'Static Text'
WHEN 1 THEN 'Frame'
WHEN 2 THEN 'Group Box'
WHEN 3 THEN 'Statis Image'
WHEN 4 THEN 'Edit Box'
WHEN 5 THEN 'Dropdown List'
WHEN 6 THEN 'Long Edit Box'
WHEN 7 THEN 'Check Box'
WHEN 8 THEN 'Radio Button'
WHEN 9 THEN 'Image'
WHEN 10 THEN 'Scroll Bar'
WHEN 11 THEN 'Subpage'
WHEN 12 THEN 'Peoplecode Command - (Button/Hyperlink Destination)'
WHEN 13 THEN 'Scroll Action - (Button/Hyperlink Destination)'
WHEN 14 THEN 'Toolbar Action - (Button/Hyperlink Destination)'
WHEN 15 THEN 'External Link - (Button/Hyperlink Destination)'
WHEN 16 THEN 'Internal Link - (Button/Hyperlink Destination)'
WHEN 17 THEN 'Process - (Button/Hyperlink Destination)'
WHEN 18 THEN 'Secondary Page'
WHEN 19 THEN 'Grid'
WHEN 20 THEN 'Tree'
WHEN 21 THEN 'Secondary Page - (Button/Hyperlink Destination)'
WHEN 23 THEN 'Horizontal Rule'
WHEN 24 THEN 'Tab Separator'
WHEN 25 THEN 'Html Area'
WHEN 26 THEN 'Prompt Action - (Button/Hyperlink Destination)'
WHEN 27 THEN 'Scroll Area'
WHEN 29 THEN 'Page Anchor'
WHEN 30 THEN 'Chart'
WHEN 31 THEN 'Push Button/Link'
WHEN 32 THEN 'Analytic Grid'
ELSE TO_CHAR(FIELDTYPE)
END CASE
from sysadm.PSPNLFIELD where pnlname = '';
Retrofit - Private Queries using below sql updates:
UPDATE SYSADM.PSQRYBIND SET OPRID = ' ' WHERE OPRID = '' AND QRYNAME = '';
UPDATE SYSADM.PSQRYCRITERIA SET OPRID = ' ' WHERE OPRID = '' AND QRYNAME = '';
UPDATE SYSADM.PSQRYDEFN SET OPRID = ' ' WHERE OPRID = '' AND QRYNAME = '';
UPDATE SYSADM.PSQRYDEL SET OPRID = ' ' WHERE OPRID = '' AND QRYNAME = '';
UPDATE SYSADM.PSQRYEXPR SET OPRID = ' ' WHERE OPRID = '' AND QRYNAME = '';
UPDATE SYSADM.PSQRYFIELD SET OPRID = ' ' WHERE OPRID = '' AND QRYNAME = '';
UPDATE SYSADM.PSQRYRECORD SET OPRID = ' ' WHERE OPRID = '' AND QRYNAME = '';
UPDATE SYSADM.PSQRYSELECT SET OPRID = ' ' WHERE OPRID = '' AND QRYNAME = '';
UPDATE SYSADM.PSQRYSTATS SET OPRID = ' ' WHERE OPRID = '' AND QRYNAME = '';
UPDATE SYSADM.PSQRYTRANS SET OPRID = ' ' WHERE OPRID = '' AND QRYNAME = '';
