Skip to Content
Information
This documentation supports the 21.3 version of BMC Helix ITSM.To view an earlier version, select the version from the Product version menu.

Examples of SQL statements for data administration types

When you modify an SQL statement, look up the example SQL statement for the applicable data administration type in the following sections.

Company

The following example is for the count action:

  • Form—CTM:People
  • Filter—DLD:DAT:CompanyCTMLargeForms_Count_Q
  • SQL statement—SELECT COUNT(C1) FROM $z1D_schemaID$ WHERE C1000000001 = '$Company$'

Related topics

Modifying-the-data-wizard-to-work-with-new-fields-on-BMC-Helix-ITSM-forms

Data-wizard-architecture-overview

Using-Data-Management-tools-for-custom-Foundation-data

The following example is for the update action:

  • Form—CTM:People
  • Filter—DLD:DAT:CompanyCTMLargeForms_Update`!_Q
  • SQL statement—UPDATE $z1D_schemaID$ SET C1000000001 = '$Company_NewVal$' WHERE C1000000001 = '$Company$'

Person name

You must use either the person ID or login ID to count or update the Person name. This ID is used in the WHERE clause of the SQL statement.

The following example is for the count action when the full name is on the form:

  • Form—CTM:Support Group Association
  • Filter—DLD:DAT:PersonNameLargeForms_Count_Q 
  • SQL statement—SELECT COUNT(C1) FROM $z1D_schemaID$ WHERE C1000000080 = '$PersonID$'

The following example is for the update action when the full name is on the form: 

  • Form—CTM:Support Group Association 
  • Filter—DLD:DAT:PersonNameLargeForms_Update`!_Q 
  • SQL statement—UPDATE $z1D_schemaID$ SET C1000000017 = '$FullName_NewVal$' WHERE C1000000080 = '$PersonID$'

The following example is for the count action when the full name, first name, and last name are on the form: 

  • Form—APR:Approver Lookup 
  • Filter—DLD:DAT:PersonNameWithLoginIDOther_Count_Q 
  • SQL statement—SELECT COUNT(C1) FROM $z1D_schemaID$ WHERE C1000000080 = '$PersonID$'

The following example is for the update action when the full name, first name, and last name are on the form: 

  • Form—APR:Approver Lookup 
  • Filter—DLD:DAT:PersonNameWithLoginIDOther_Count`!_Q 

  • SQL statement: 

    UPDATE $z1D_schemaID$ SET
    C1000000637 = CASE WHEN (C1000000080 = '$PersonID$') THEN '$FullName_NewVal$' ELSE C1000000637 END,
    C1000000369 = CASE WHEN (C1000000080 = '$PersonID$') THEN '$FirstName_NewVal$' ELSE C1000000369 END,
    C1000000367 = CASE WHEN (C1000000080 = '$PersonID$') THEN '$LastName_NewVal$' ELSE C1000000367 END
    WHERE (C1000000080 = '$PersonID$')

Login ID

The following example is for the count action:

  • Form—CTM:People WorkLog
  • Filter—DLD:DAT:LoginIDCTM2_Count_Q 
  • SQL statement—SELECT COUNT(C1) FROM $z1D_schemaID$ WHERE C2 = '$LoginID_OriVal$' OR C5 = '$LoginID_OriVal$' OR C1000000159 = '$LoginID_OriVal$'

The following example is for the update action: 

  • Form—CTM:People WorkLog 
  • Filter—DLD:DAT:LoginIDCTM2_Count`!_Q 

  • SQL statement:

    UPDATE $z1D_schemaID$ SET
    C2 = CASE C2 WHEN '$LoginID_OriVal$' THEN '$LoginID_NewVal$' ELSE C2 END,
    C1000000159 = CASE C1000000159 WHEN '$LoginID_OriVal$' THEN '$LoginID_NewVal$' ELSE C1000000159 END,
    C5 = CASE C5 WHEN '$LoginID_OriVal$' THEN '$LoginID_NewVal$' ELSE C5 END
    WHERE (C2 = '$LoginID_OriVal$' OR C1000000159 = '$LoginID_OriVal$' OR C5 = '$LoginID_OriVal$')

Generic categorization

To count or update categorizations, you need a filter for each level of categorization on the form. Whether a filter runs depends on which tiers the user specifies on the Data Wizard Console with new values.

For example, if Tier 1, 2, and 3 are on the form, then three separate filters are needed to perform the action. One filter contains the SQL statement to count or update for all three tiers. Another filter will act on the first two tiers. Another filter acts on just the first tier. On the Data Wizard Console, if the user specifies all three tiers with new values, all three filters run. If the user specifies a new values for only Tier 1, only the filter containing Tier 1 runs.

If only Tier 1 and 2 are on the form, you need only two filters.

Category type is used in the SQL statements. If the category type is always the same for categorization, omit this field from the SQL statement. Otherwise, hard code the value in the SQL statement.

The following example is for the count action when all three tiers are on the form:

  • Form—CFG:GenericProdServiceAssoc 
  • Filter—DLD:DAT:GenCatTier3LargeForms_Count_Q

  • SQL statement:

    SELECT COUNT(C1)  FROM $z1D_schemaID$ WHERE
    C1000000744 = '$Generic Categorization Tier 1$'  AND C1000000745
    = '$Generic Categorization Tier 2$' AND C1000000746 = '$Generic
    Categorization Tier 3$'     AND C1000002257 = '$Category Type$'

The following example is for the update action when all three tiers are on the form:

  • Form—CFG:GenericProdServiceAssoc
  • Filter—DLD:DAT:GenCatTier3LargeForms_Update`!_Q

  • SQL statement:

    UPDATE $z1D_schemaID$ SET C1000000744 =
    '$GenericCatTier1_NewVal$', C1000000745 =
    '$GenericCatTier2_NewVal$', C1000000746 =
    '$GenericCatTier3_NewVal$' WHERE C1000000744 =
    '$Generic Categorization Tier 1$' AND C1000000745 =
    '$Generic Categorization Tier 2$' AND C1000000746 =
    '$Generic Categorization Tier 3$' AND C1000002257 = '$Category Type$'

The following example is for the count action when the first two tiers are on the form:

  • Form—CFG:GenericProdServiceAssoc
  • Filter—DLD:DAT:GenCatTier2LargeForms_Count_Q

  • SQL statement:

    SELECT COUNT(C1)  FROM $z1D_schemaID$ WHERE
    C1000000744 = '$Generic Categorization Tier 1$'  AND C1000000745 =
    '$Generic Categorization Tier 2$' AND C1000002257 = '$Category Type$'

The following example is for the update action when the first two tiers are on the form:

  • Form—CFG:GenericProdServiceAssoc
  • Filter—DLD:DAT:GenCatTier2LargeForms_Update`!_Q

  • SQL statement:

    UPDATE $z1D_schemaID$ SET C1000000744 =
    '$GenericCatTier1_NewVal$', C1000000745 =
    '$GenericCatTier2_NewVal$' WHERE C1000000744 =
    '$Generic Categorization Tier 1$' AND C1000000745 =
    '$Generic Categorization Tier 2$' AND C1000002257 = '$Category Type$'

Operational categorization

Like generic categorization, operational categorization has multiple tiers of categorization that require multiple filters.

The following example is for the count action when two tiers are on the form:

  • Form—CFG:GenericProdServiceAssoc
  • Filter—DLD:DAT:OperCatTier2LargeForms_Count_Q
  • SQL statement—SELECT COUNT(C1) FROM $z1D_schemaID$ WHERE C1000000063 = '$Categorization Tier 1$' AND C1000000064 = '$Categorization Tier 2$'

The following example is for the update action when two tiers are on the form:

  • Form—CFG:GenericProdServiceAssoc
  • Filter—DLD:DAT:OperCatTier2LargeForms_Update`!_Q
  • SQL statement—UPDATE $z1D_schemaID$ SET C1000000063 = '$OperationalCatTier1_NewVal$', C1000000064 = '$OperationalCatTier2_NewVal$' WHERE C1000000063 = '$Categorization Tier 1$' AND C1000000064 = '$Categorization Tier 2$'

The following example is for the count action for tier 1:

  • Form—CFG:GenericProdServiceAssoc
  • Filter—DLD:DAT:OperCatTier1LargeForms_Count_Q
  • SQL statement—SELECT COUNT(C1) FROM $z1D_schemaID$ WHERE C1000000063 = '$Categorization Tier 1$'

The following example is for the update action for tier 1:

  • Form—CFG:GenericProdServiceAssoc
  • Filter—DLD:DAT:OperCatTier1LargeForms_Update`!_Q
  • SQL statement—UPDATE $z1D_schemaID$ SET C1000000063 = '$OperationalCatTier1_NewVal$' WHERE C1000000063 = '$Categorization Tier 1$'

Product categorization

Like generic categorization, product categorization has multiple tiers of categorization that require multiple filters.

The following example is for the count action for all three tiers:

  • Form—CFG:Group Event Mapping 
  • Filter—DAT:ProdCatAllTiersOther1_Count_Q 

  • SQL statement:

    SELECT COUNT(C1)  FROM $z1D_schemaID$ WHERE
    (C200000003 = '$Product Categorization Tier 1$'  AND C200000004 =
    '$Product Categorization Tier 2$' AND C200000005 =
    '$Product Categorization Tier 3$'  AND C240001002 = '$Product Name$' AND
    C240001003 = '$Manufacturer$')

The following example is for the update action for all three tiers: 

  • Form—CFG:Group Event Mapping 
  • Filter—DLD:DAT:ProdCatAllTiersOther4_Update`!_Q 

  • SQL statement:

    UPDATE $z1D_schemaID$ SET C200000003 =
    '$ProductCatTier1_NewVal$', C200000004 =
    '$ProductCatTier2_NewVal$', C200000005 =
    '$ProductCatTier3_NewVal$', C240001002 = '$ProductName_NewVal$',
    C240001003 = '$Manufacturer_NewVal$' WHERE C200000003 = '$Product
    Categorization Tier 1$'     AND C200000004 = '$Product Categorization Tier 2$'
    AND C200000005 = '$Product Categorization Tier 3$' AND
    C240001002 = '$Product Name$' AND C240001003 = '$Manufacturer$'

Produce model and version

The Product Name, Manufacturer, and Product Model/Version fields are referenced in the WHERE clause of the SQL statement.

The following example is for the count action:

  • Form—CFG:Group Event Mapping
  • Filter—DLD:DAT:ProdMVOther_Count_Q
  • SQL statement—SELECT COUNT(C1) FROM $z1D_schemaID$ WHERE (C240001002 = '$ProductName_MV$' AND C240001003 = '$Manufacturer_MV$' AND C240001005 = '$Product Model/Version$')

The following example is for the update action:

  • Form—CFG:Group Event Mapping
  • Filter—DLD:DAT:ProdMVPCTCFG_Update`!_Q
  • SQL statement—UPDATE $z1D_schemaID$ SET C240001005 = '$ProductMV_NewVal$' WHERE C240001002 = '$ProductName_MV$' AND C240001003 = '$Manufacturer_MV$' AND C240001005 = '$Product Model/Version$'

Site name

The following example is for the count action:

  • Form—CTM:People Template
  • Filter—DLD:DAT:SiteOther_Count_Q
  • SQL statement—SELECT COUNT(C1) FROM $z1D_schemaID$ WHERE C260000001 = '$Site$'

The following example is for the update action:

  • Form—CTM:People Template
  • Filter—DLD:DAT:SiteOther_Update`!_Q
  • SQL statement—UPDATE $z1D_schemaID$ SET C260000001 = '$Site_NewVal$' WHERE C260000001 = '$Site$'

Site information

The Site Name field is referenced in the WHERE clause of the SQL statement

The following example is for the count action:

  • Form—SIT:Site
  • Filter—DLD:DAT:SiteInfoLargeForms_Count_Q
  • SQL statement—SELECT COUNT(C1) FROM $z1D_schemaID$ WHERE C260000001 = '$SiteName_SiteInfo$'

The following example is for the update action:

  • Form—SIT:Site
  • Filter—DLD:DAT:SiteInfoLargeForms_Update`!_Q

  • SQL statement:

    UPDATE $z1D_schemaID$ SET C1000000004 =
    '$City_NewVal$', C1000000002 = '$Country_NewVal$',  C1000000037 =
    '$Street_NewVal$', C1000000003 = '$StateProvince_NewVal$',
    C1000000039 = '$ZipPostalCode_NewVal$', C1000000541 =
    '$TimeZone_NewVal$' WHERE C260000001 = '$SiteName_SiteInfo$'

Support group

To count or update the support group, you might need multiple filters, depending on the number of levels on the form. Whether a filter runs depends on which tiers the user selects on the Data Wizard Console.

For example, if Support Organization and Support Group are on the form, then you need two filters to perform the action. One filter will have the SQL statements to count and update for Support Organization and Support Group; the other filter acts on only the Support Organization. If all tiers are filled in for the new value on the console, both filters run. If only Support Organization is filled in, only the filter containing Support Organization runs.

The following example is for the count action for both the Support Organization and Support Group fields:

  • Form—CTM:People Permission Groups
  • Filter—DLD:DAT:SGPAllLargeForms_Count_Q
  • SQL statement—SELECT COUNT(C1) FROM $z1D_schemaID$ WHERE C1000000001 = '$SupportCompany_SGP$' AND C301363000 = '$Support Organization$' AND C301363100 = '$Support Group Name$'

The following example is for the update action for both the Support Organization and Support Group fields:

  • Form—CTM:People Permission Groups
  • Filter—DLD:DAT:SGPAllLargeForms_Update`!_Q

  • SQL statement:

    UPDATE $z1D_schemaID$ SET C301363000 =
    '$SupportOrganization_NewVal$', C301363100 =
    '$SupportGroupName_NewVal$' WHERE C1000000001 =
    '$SupportCompany_SGP$' AND C301363000 =
    '$Support Organization$' AND C301363100 = '$Support Group Name$'

The following example is for the count action for the Support Group field only:

  • Form—CTM:People Permission Groups
  • Filter—DLD:DAT:SGPOrgLargeForms_Count_Q
  • SQL statement—SELECT COUNT(C1) FROM $z1D_schemaID$ WHERE C1000000001 = '$SupportCompany_SGP$' AND C301363000 = '$Support Organization$'

The following example is for the update action for the Support Group field only:

  • Form—CTM:People Permission Groups
  • Filter—DLD:DAT:SGPOrgLargeForms_Update`!_Q
  • SQL statement—UPDATE $z1D_schemaID$ SET C301363000 = '$SupportOrganization_NewVal$' WHERE C1000000001 = '$SupportCompany_SGP$' ANDC301363000 = '$Support Organization$'

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC Helix ITSM 21.3