Queries related to the datastore parameters

From SMT-X
Jump to: navigation, search

Below are some example queries that run on the parameter data table: update processinstance set

           ticketdescription=field6, 
           ticketinformation=field27, 
           ticketsolution='ActualFinish: ' + Field7 + '
' + replace(field26,char(13),'
'), ticketpoolid = 14, ticketclassification = '3W', ticketstatus = 'Closed', ticketMedium = 'HPSD', TicketService = '3W', TicketFieldsLocked = 1 from parameterdata where parameter_id=1058 and processinstance.id=field5 and Field8='281521429887887'

Save HPSD Ticketnummer

     insert into ProcessInstTicketCustomFldVal 
     select field5, 6, min(field1) from ParameterData where Parameter_Id=1058 and Field8='281521429887887' group by field5 

Save Instruction

     insert into ProcessInstTicketCustomFldVal 
     select field5, 5, field28 from ParameterData inner join ProcessInstTicketCustomFldVal on stringvalue=field1 where Parameter_Id=1058 and Field8='281521429887887' and field28 is not null and field28 <>  group by field5,field28

Add remark to history

     insert into ProcessInstanceMessage 
     select field5, getdate(), 1, 'Actie & Analyse geïmporteerd vanuit HPSD in het Instructie veld', 6,3,0 from ParameterData where Parameter_Id=1058 and Field8='281521429887887' 

Field1 to field20 and field26 to field50 are varchar(1000) fields. Field21-field25 are unlimited varchar fields.

Read out full contents of datastore parameter

    SELECT FIELD1 as Name, Field2 as Name2 from parameterdata where parameter_id=123