SQL Patronen voor Ultimo Consultants

SQL is onmisbaar voor Ultimo consultants — van data-imports en troubleshooting tot rapportage en bulk-wijzigingen. Dit artikel bundelt de praktische patronen en voorbeelden die elke consultant moet kennen.

Gerelateerd: sql-referentie, implementatie, import-connectors, entities/equipment, entities/job

Overzicht: twee uitvoeringsomgevingenbewerken

Let op: Gebruik altijd het schema-prefix dba. bij alle tabellen (bijv. dba.equipment, dba.job). In de UC tool is dit al ingesteld; in SSMS moet je dit zelf meegeven.

Verbinding maken met SSMSbewerken

1. Open SSMS en kies Connect Object Explorer
2. Server name: URL van de Ultimo-omgeving
3. Authentication: Gebruikersnaam + wachtwoord
4. Klik op Options > tabblad Connection Properties
5. Connect to Database: voer de databasecode in (bijv. CI12345)

Valkuil: Zonder de databasecode verbind je met de verkeerde database. De databasecode staat in de Ultimo omgevings-URL of is op te vragen bij de klant / Ultimo Support.

Ultimo tabel- en kolomconventiesbewerken

Ultimo gebruikt een vaste naamgevingsconventie: elke tabel heeft een prefix die terugkomt in alle kolomnamen. De primary key volgt altijd het patroon <PREFIX>_ID.

Foreign keys volgen het patroon <BRONTABEL_PREFIX><DOELTABEL_PREFIX>_ID, bijvoorbeeld:

• EQM_DEP_ID → DEP_ID (equipment heeft een afdeling)
• JOB_EQM_ID → EQM_ID (job is gekoppeld aan equipment)
• JOB_WHAT_ID → WHAT_ID (job heeft een werkordertype)

Context-waardenbewerken

RecStatus-waardenbewerken

Kolomnamen vinden via Technical Screen Informationbewerken

Je hoeft kolomnamen niet uit het hoofd te kennen. Zo vind je ze direct in Ultimo:

1. Zet de cursor in het veld waarvan je de kolomnaam wilt weten
2. Klik op het ?-icoon rechtsboven in het scherm (Technical Screen Information)
3. Het venster toont de tabel en de kolomnaam van dat veld

Combineer dit met de UC tool > Database Objects om de entiteitsnaam te vinden, en daarna Documentation > Entity Overview om de property-naam te vertalen voor gebruik in workflows.

SELECT queriesbewerken

Basispatroonbewerken

SELECT kolom1, kolom2
FROM dba.tabel
WHERE conditie
ORDER BY kolom1;

Voorbeeld: equipment per afdelingbewerken

SELECT EQM_ID AS Code,
       EQM_Description AS Naam,
       DEP_Description AS Afdeling
FROM dba.equipment
INNER JOIN dba.department ON EQM_DEP_ID = DEP_ID
WHERE EQM_Context = 1
ORDER BY DEP_Description;

Voorbeeld: actieve werkordersbewerken

SELECT JOB_ID, JOB_Description, JOB_RecStatus
FROM dba.job
WHERE JOB_RecStatus > 0
ORDER BY JOB_ID DESC;

WHERE clause patronenbewerken

Praktijkvoorbeeld: equipment zonder afdelingbewerken

SELECT EQM_ID, EQM_Description
FROM dba.equipment
WHERE EQM_DEP_ID IS NULL
  AND EQM_Context = 1
  AND EQM_RecStatus > 0;

JOIN operatiesbewerken

INNER JOIN — alleen overeenkomende recordsbewerken

Filtert records weg waarbij de foreign key NULL is. Gebruik dit als je zeker weet dat elke rij een koppeling heeft.

SELECT JOB_ID, EQM_Description, DEP_Description
FROM dba.job
INNER JOIN dba.equipment ON JOB_EQM_ID = EQM_ID
INNER JOIN dba.department ON EQM_DEP_ID = DEP_ID
WHERE JOB_RecStatus > 0;

LEFT OUTER JOIN — alle records uit de linker tabelbewerken

Inclusief jobs zonder gekoppeld equipment. De equipment-kolommen zijn dan NULL.

SELECT JOB_ID, JOB_Description, EQM_Description
FROM dba.job
LEFT OUTER JOIN dba.equipment ON JOB_EQM_ID = EQM_ID
WHERE JOB_RecStatus > 0;

RIGHT OUTER JOIN / FULL OUTER JOINbewerken

• RIGHT OUTER JOIN: alle records uit de rechter tabel (zelfs als er geen jobs zijn)
• FULL OUTER JOIN: alle records uit beide tabellen, NULL waar geen match

SELF JOIN — hiërarchieënbewerken

Handig voor parent-child relaties binnen dezelfde tabel (bijv. equipment met sub-equipment):

SELECT parent.EQM_ID AS Parent_Code,
       parent.EQM_Description AS Parent_Naam,
       child.EQM_ID AS Child_Code,
       child.EQM_Description AS Child_Naam
FROM dba.equipment AS parent
INNER JOIN dba.equipment AS child ON child.EQM_ParentID = parent.EQM_ID
WHERE parent.EQM_Context = 1;

Valkuil: INNER JOIN filtert stilzwijgend alle records weg waarbij de foreign key NULL is. Dit kan telresultaten vertekenen. Gebruik LEFT JOIN als je ook records zonder koppeling wilt zien.

Aggregatiefuncties met GROUP BYbewerken

Beschikbare functiesbewerken

Voorbeeld: werkorders per equipment en typebewerken

SELECT COUNT(JOB_WHAT_ID) AS Aantal,
       EQM_Description AS Equipment,
       WHAT_Description AS Werkordertype
FROM dba.job
INNER JOIN dba.equipment ON JOB_EQM_ID = EQM_ID
INNER JOIN dba.work_order_type ON JOB_WHAT_ID = WHAT_ID
WHERE EQM_Context = 1
GROUP BY EQM_Description, WHAT_Description
HAVING COUNT(JOB_WHAT_ID) > 1
ORDER BY Aantal DESC;

Regel: Elke kolom in SELECT die geen aggregatiefunctie is, moet ook in GROUP BY staan. Gebruik HAVING (niet WHERE) om op het resultaat van een aggregatiefunctie te filteren.

INSERT — records aanmakenbewerken

Verplichte kolommen bij elke INSERTbewerken

Bij het aanmaken van records in Ultimo zijn de volgende kolommen altijd verplicht:

CompID opzoekenbewerken

UC tool > Settings > Companies — selecteer het juiste bedrijf en noteer de Company ID. Bij meerdere bedrijven kies je het bedrijf van de klant.

Voorbeeld: employee aanmakenbewerken

INSERT INTO dba.employee (
    AMP_ID,
    AMP_Description,
    AMP_Context,
    AMP_RecStatus,
    AMP_CompID
)
VALUES (
    'EMP001',
    'Jan de Vries',
    1,
    1,
    'C001'
);

Valkuil: Vergeten van CompID, Context of RecStatus leidt tot records die niet zichtbaar zijn in de applicatie of tot validatiefouten.

UPDATE — records wijzigenbewerken

Altijd een WHERE-clausule gebruiken!bewerken

-- GEVAARLIJK: past ALLE records aan
UPDATE dba.equipment SET EQM_Description = 'Test';

-- CORRECT: alleen het gewenste record
UPDATE dba.equipment
SET EQM_Description = 'Pomp unit 01'
WHERE EQM_ID = 'EQP001';

Veld leegmakenbewerken

UPDATE dba.job
SET JOB_EQM_ID = NULL
WHERE JOB_ID = 'JOB001';

Waarden combineren met CONCATbewerken

UPDATE dba.employee
SET AMP_Description = CONCAT(AMP_Description, ' (Inactief)')
WHERE AMP_RecStatus = 0;

Naar hoofdletters omzettenbewerken

UPDATE dba.department
SET DEP_ID = UPPER(DEP_ID)
WHERE DEP_Context = 1;

DELETE — records verwijderenbewerken

Altijd een WHERE-clausule gebruiken!bewerken

-- GEVAARLIJK: verwijdert ALLE records
DELETE FROM dba.equipment;

-- CORRECT: alleen het gewenste record
DELETE FROM dba.equipment
WHERE EQM_ID = 'EQP001';

Foreign key constraintsbewerken

Ultimo heeft referentiële integriteit. Verwijder altijd eerst de afhankelijke records, dan pas het hoofdrecord:

-- Stap 1: verwijder gekoppelde jobs
DELETE FROM dba.job
WHERE JOB_EQM_ID = 'EQP001';

-- Stap 2: verwijder het equipment-record
DELETE FROM dba.equipment
WHERE EQM_ID = 'EQP001';

Tip: Voor complexe verwijderingen met veel afhankelijkheden gebruik je de stored procedure dba.applsp_DeletePrimaryKey — zie sql-referentie voor het patroon met dba.IdConversion.

Data import vanuit Excelbewerken

Het standaard importpatroon voor Ultimo werkt via een tijdelijke tabel (_temp_-prefix) en OPENROWSET. Dit patroon is veilig, herhaalbaar en leverbaar als script aan Ultimo Support.

Vereistenbewerken

• Access Database Engine drivers geïnstalleerd (download-link staat bovenaan het trainingsscript op het LMS)
• Excel-bestand moet gesloten zijn tijdens het uitvoeren van de OPENROWSET-query
• AutoKey-breedte opzoeken: UC tool > Settings > Auto Keys > filter op de entiteit

Stap 1: tijdelijke tabel aanmaken en vullen via OPENROWSETbewerken

-- Verwijder de tijdelijke tabel als die al bestaat
IF OBJECT_ID('dba._temp_equipment_import') IS NOT NULL
    DROP TABLE dba._temp_equipment_import;

-- Laad data uit Excel naar tijdelijke tabel
SELECT *
INTO dba._temp_equipment_import
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml; HDR=YES; Database=C:\temp\voorbeeld1.xlsx',
    'SELECT * FROM [Sheet1$]'
);

Valkuil: Als het Excel-bestand open staat, geeft OPENROWSET een fout: "already opened exclusively by another user". Sluit het bestand altijd vóór uitvoer.

Stap 2: controleer de tijdelijke tabelbewerken

SELECT * FROM dba._temp_equipment_import;

Controleer of alle rijen correct zijn geladen voordat je doorgaat.

Stap 3: INSERT naar de doeltabel met AutoKeybewerken

INSERT INTO dba.equipment (
    EQM_ID,
    EQM_Context,
    EQM_RecStatus,
    EQM_CompID,
    EQM_ExternalID,
    EQM_DataProvider
)
SELECT
    RIGHT(REPLICATE('0', 5) + CAST(AUK_ID AS VARCHAR), 5),  -- AutoKey, breedte 5
    1,                        -- Context: Technische Dienst
    1,                        -- RecStatus: Actief
    'C001',                   -- CompID: zie UC tool > Settings > Companies
    t.Col1,                   -- ExternalID = kolom 1 uit Excel (jouw referentie-tag)
    '2024_1105_DBS'           -- DataProvider: jaar_datum_initialen
FROM dba._temp_equipment_import t
CROSS JOIN dba.autokey
WHERE AUK_ID = (SELECT MAX(AUK_ID) + ROW_NUMBER() OVER (ORDER BY t.Col1)
                FROM dba.autokey WHERE AUK_TableName = 'equipment');

Eenvoudiger AutoKey-patroon (uit trainingsscript):

INSERT INTO dba.equipment (
    EQM_ID,
    EQM_Context,
    EQM_RecStatus,
    EQM_CompID,
    EQM_ExternalID,
    EQM_DataProvider
)
SELECT
    RIGHT('00000' + CAST(ROW_NUMBER() OVER (ORDER BY Col1) +
          (SELECT MAX(CAST(AUK_CurrentValue AS INT)) FROM dba.autokey
           WHERE AUK_TableName = 'equipment') AS VARCHAR), 5),
    1,
    1,
    'C001',
    Col1,
    '2024_1105_DBS'
FROM dba._temp_equipment_import;

AutoKey-breedte: Controleer in UC tool > Settings > Auto Keys de huidige waarde van het equipment AutoKey (bijv. 39936). De breedte is het aantal cijfers (hier 5). Pas de RIGHT('00000'...) aan op de werkelijke breedte.

Stap 4: UPDATE met informatie uit de tijdelijke tabelbewerken

UPDATE dba.equipment
SET EQM_Description = t.Col2,
    EQM_DEP_ID = t.Col3
FROM dba.equipment e
INNER JOIN dba._temp_equipment_import t
    ON e.EQM_ExternalID = t.Col1 COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE e.EQM_DataProvider = '2024_1105_DBS';

COLLATE SQL_Latin1_General_CP1_CI_AS is nodig om encoding-verschillen tussen Excel-tekst en de Ultimo-database te overbruggen. Zonder COLLATE kan de match mislukken.

DataProvider als filter: Door te filteren op EQM_DataProvider raak je alleen de records aan die je zojuist hebt aangemaakt — niet eerder bestaande records.

Stap 5: tijdelijke tabel opruimenbewerken

DROP TABLE dba._temp_equipment_import;

Stap 6: script genereren voor productie (Generate Scripts)bewerken

Na succesvolle import op de testomgeving lever je een script aan Ultimo Support voor de productieomgeving:

1. In SSMS: rechtermuisklik op de database in Object Explorer
2. Kies Tasks > Generate Scripts
3. Kies Select specific database objects > selecteer de tijdelijke tabel (_temp_equipment_import)
4. Klik op Advanced:
   • Script USE Database: stel in op False (anders werkt het script alleen op jouw database)
   • Types of data to script: stel in op Schema and data (anders worden de Excel-rijen niet meegenomen)
5. Sla op als .sql-bestand of open in een nieuw queryvenster
6. Voeg handmatig de INSERT- en UPDATE-stappen toe aan het gegenereerde script
7. Voeg onderaan een DROP TABLE dba._temp_equipment_import; toe
8. Lever het complete script aan Ultimo Support

Best practicesbewerken

Veelvoorkomende foutenbewerken

UPDATE of DELETE zonder WHEREbewerken

-- Dit past ALLE equipment-records aan!
UPDATE dba.equipment SET EQM_Description = 'Test';

Oplossing: Voer altijd eerst een SELECT uit met dezelfde WHERE-conditie om te controleren hoeveel records worden geraakt.

Excel-bestand is nog openbewerken

Msg 7303: Cannot initialize the data source object of OLE DB provider
"Microsoft.ACE.OLEDB.12.0"

Oplossing: Sluit het Excel-bestand volledig af (ook als ander gebruiker het open heeft) en voer de OPENROWSET-query opnieuw uit.

Verkeerde AutoKey-breedtebewerken

De AutoKey heeft 5 cijfers maar je script gebruikt 6 — de primary key klopt dan niet meer met de reeks.

Oplossing: Controleer de huidige waarde in UC tool > Settings > Auto Keys en pas de RIGHT(REPLICATE('0', N) + ...) aan op de werkelijke breedte.

Foreign key constraint bij DELETEbewerken

Msg 547: The DELETE statement conflicted with the REFERENCE constraint

Oplossing: Verwijder eerst alle afhankelijke records (bijv. jobs gekoppeld aan equipment) voordat je het hoofdrecord verwijdert. Of gebruik dba.applsp_DeletePrimaryKey.

INNER JOIN filtert onverwacht records wegbewerken

Je verwacht 100 equipment-records maar krijgt er 80. De 20 missing records hebben EQM_DEP_ID = NULL.

Oplossing: Vervang INNER JOIN door LEFT JOIN als je ook records zonder koppeling wilt zien.

CompID, Context of RecStatus vergeten bij INSERTbewerken

Records zijn aangemaakt maar niet zichtbaar in de applicatie.

Oplossing: Controleer altijd of CompID, Context en RecStatus zijn meegegeven. Voeg ze toe via een UPDATE of verwijder de records en maak ze opnieuw aan.

COLLATE ontbreekt bij UPDATE-matchbewerken

De UPDATE heeft 0 rows affected, maar de records en de tijdelijke tabel bevatten dezelfde waarden.

Oplossing: Voeg COLLATE SQL_Latin1_General_CP1_CI_AS toe aan de join-conditie:

ON e.EQM_ExternalID = t.Col1 COLLATE SQL_Latin1_General_CP1_CI_AS

Gerelateerde artikelenbewerken

• sql-referentie — Stored procedures, maintenance mode, verwijderpatronen
• implementatie — Implementatieworkflow, oplevering aan klant
• import-connectors — Import/Export Connectors als alternatief voor directe SQL-imports
• entities/equipment — Equipment entiteit: velden, relaties, context-waarden
• entities/job — Job entiteit: velden, statussen, koppelingen

Brondatabewerken

Dit artikel is consultant-synthese. Voor ground-truth data over specifieke Ultimo-objecten gebruik de onderstaande tools.

• Entiteit-data — lookup_entity("<Name>") · lookup_table_schema("<Name>") Alle properties, DB-kolomnamen, triggers en computed columns. Bronnen: Entities.xml, database-schema.json.
• Workflows per entiteit — find_workflows("", entity="<Name>") Alle Before/After Save events en andere ActionFields voor een entiteit. Bron: workflows.xml.
• Schermen — lookup_screen("<ScreenName>") · Schermen index Schermdefinities incl. tabel, autorisatielevel, screen-level. Bron: ultimo_screens_names.xml.
• AET-settings / feature toggles — find_aet_settings(query) · AET index Feature toggles en systeem-configuratie. Bron: ApplicationElementTreeData.json.
• Kennisbank-breed zoeken — search(query) Doorzoekt alle wiki-artikelen, entities, workflows, schermen, templates en ActionFields tegelijk.