Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Stardust/Knowledge Base/Infrastructure System Administration Maintenance/SQL
< Stardust | Knowledge Base | Infrastructure System Administration Maintenance
Revision as of 01:19, 21 October 2011 by Srinivasan.iyer.sungard.com (Talk | contribs) (→SQL Commands Cookbook)
SQL Commands Cookbook
NOTE to authors: Please make sure to specify the version on which a command was executed
[All version numbers < 7.0 refer to the Infinity Process Platform (IPP)]
Retrieving structured data initialized with non-null or null value (plus non-leaf data in the data structure)
(Versions: 5.3.x)
SELECT pd.ID, pi.OID, SUBSTR(sd.XPATH,1,20) as "XPATH", SUBSTR(sdv.STRING_VALUE,1,20) as "STRING_VALUE", sdv.NUMBER_VALUE FROM PROCESS_INSTANCE pi, PROCESS_DEFINITION pd, DATA_VALUE dv, STRUCTURED_DATA sd, STRUCTURED_DATA_VALUE sdv WHERE pd.OID = pi.processDefinition and pi.OID = dv.processInstance and dv.data = sd.data and sd.OID = sdv.XPATH and pi.OID = sdv.processInstance;
Retrieving structured non-initialized data
(Versions: 5.3.x)
SELECT pd.ID, pi.OID, SUBSTR(sd.XPATH,1,20) as "XPATH" FROM PROCESS_INSTANCE pi, PROCESS_DEFINITION pd, DATA_VALUE dv, STRUCTURED_DATA sd WHERE pd.OID = pi.processDefinition and pi.OID = dv.processInstance and dv.data = sd.data and sd.OID not in ( select sdv2.XPATH from structured_data_value sdv2 where sdv2.processinstance = pi.OID );