<< Click to Display Table of Contents >> Navigation:  Designers > Query Builder > sql* Functies

 

De door Logicworks CRM ondersteunde databases (Microsoft Access, Microsoft SQL Server en MySQL) maken allemaal gebruik van min-of-meer standaard taal (SQL) om gegevens uit de database op te halen en te bewerken. In de afgelopen jaren hebben database-leveranciers echter de nodige uitbreidingen aangebracht op hun SQL-versies, waardoor diverse dialecten zijn ontstaan. Dit is op zich geen probleem, zolang Logicworks CRM niet wordt gemigreerd naar een andere database.

 

In Logicworks CRM kan gebruik gemaakt worden van ingebouwde sql* functies. Deze genereren tijdens het uitvoeren van de selectie een database-specifiek SQL-statement. Het is verstandig om direct vanaf het begin gebruik te maken van de sql*-functies, zodat bij een eventuele databasemigratie van bijvoorbeeld Access naar SQL Server alle lijsten en overzichten probleemloos blijven functioneren.

 

De volgende sql* functies worden momenteel ondersteund:

 

sqlAge( <startdatum:date> [, <einddatum:date>] )

Deze functie toont het verschil in dagen tussen de opgegeven start- en einddatum. Wordt de einddatum niet ingevuld, dan wordt de huidige datum als einddatum gebruikt.  

 

 

sqlCNull( <kolom>, <default waarde> )

Het kan voorkomen dat bepaalde kolommen geen waarde bevatten of een kolom na het koppelen van diverse tabellen leeg is. In deze gevallen kunt u gebruik maken van deze functie om de NULL te vervangen door een standaard vaste waarde.

 

 

sqlConcat( <kolom1> & <kolom2> & ... )

Wilt u meerdere kolommen of teksten samenvoegen in 1 uitvoerkolom, gebruik deze functie dan.

 

 

sqlDate()

Genereert een database-specifieke waarde van de huidige datum, welke kan worden gebruikt bij het selecteren van gegevens op bepaalde data.

 

 

sqlIIF( <conditie>, <waar>, <onwaar> )

Afhankelijk van de gebruikte conditie wordt als resultaat <waar> of <onwaar> als uitvoerwaarde gebruikt.

 

U wilt bijvoorbeeld alle contactpersonen tonen, welke de volgende maand jarig zijn. Voor de maanden januari t/m november is dit vrij eenvoudig te realiseren door de maand van de geboortedatum te vergelijken met de huidige maand: MONTH(sqlDate()) + 1 = MONTH(contacts.birthday). In december gaat deze vergelijking echter niet op, aangezien we dan willen nagaan of de verjaardag in januari valt. In deze situatie komt de functie sqlIIF() van pas en kan de volgende conditie worden gebruikt: sqlIIF( MONTH(sqlDate()) = 12, 1, MONTH(sqlDate() ) + 1 ) = MONTH(contacts.birthday).

 

 

sqlMinutesToTime( <kolom:numeriek> )

Converteert een numerieke kolom waarin de duur is vastgelegd in minuten naar een tijd. Een kolomwaarde van bijvoorbeeld 153 (minuten) zal in het resultaat worden getoond als '02:33'.

 

 

sqlNow()

Genereert een database-specifieke waarde van de actuele datum/tijd, welke kan worden gebruikt bij het selecteren van gegevens op bepaalde datum en tijd.

 

 

sqlQuery( <queryid> )

In een aantal situaties is het niet mogelijk om met 1 query het gewenste overzicht of grafiek te definieren. Er is dan vaak behoefte aan een sub-query, welke vooraf een aantal (on)geldige waarden selecteert uit de database. Vervolgens kan in de Query gebruik gemaakt worden van het resultaat van de sub-query.

 

Voorbeeld: U wilt weten regelmatig controleren of van Bedrijven het vestigingsadres wel is ingevuld. Met een standaard Query is het niet mogelijk dit te bepalen, omdat aan de meeste Bedrijven meerdere adressen zijn gekoppeld. Het resultaat van deze selectie zouden alle andere adressen zijn en bedrijven zonder vestigingsadres, maar met postadres zouden ook als geldig worden aangemaakt. Met de functie SqlQuery() kunnen we dit probleem  als volgt oplossen:

 

•Selecteer in de subquery de 'Bedrijf ID' (accountid) van alle Bedrijven met een vestigingsadres.

•Sla de subquery op en bepaal de unieke sleutel van deze query via de Query Builder lijstweergave, stel dit is 50010.

•Maak een tweede query en geef als filter voor 'Bedrijf ID' de volgende conditie op: NOT IN SqlQuery( 50010 ).

•Het resultaat van de Query zullen alle Bedrijven zijn welke niet voorkomen in de gedefinieerde subquery.

 

 

sqlToDate( <kolom:datetime>)

Converteert de waarde van een kolom van het type DateTime (=datum/tijd) naar een datum zonder tijdstip.

 

 

sqlToDecimal( <kolom:numeriek> )

Het resultaat type van een berekening wordt in SQL Server en MySQL altijd bepaald door het eerst gebruikte veld in de berekening.

 

Voorbeeld: de duur van een taak wordt altijd vastgelegd in minuten. In de lijstweergave zal echter vaak de omrekening naar uren worden gemaakt. Dit kan in de Query Builder eenvoudigweg door het aantal minuten te delen door 60. Het resultaat van bijvoorbeeld 80 minuten / 60 wordt echter als 1,00 getoond, omdat het minuten-veld een integer is zonder decimalen. Het resultaat wordt daarom altijd van het type Integer. Wilt u zeker zijn dat het resultaat ook de decimalen weergeeft, dan dient u het minuten-veld eerst om te zetten naar een decimaal veldtype. In de Query Builder zou u de berekening laboritems.actualduration / 60 om kunnen zetten naar sqlDecimal( laboritems.actualduration) / 60.

 

 

sqlToEmptyString( <kolom:varchar> )

Als in een databaseselectie het resultaat geen lege tekstwaarde, maar een NULL-waarde oplevert, dan kan dit soms vervelende gevolgen hebben. U kunt in dat geval met de functie sqlToEmptyString() de kolommen welke een NULL-waarde opleveren omzetten naar een lege tekst.

 

 

sqlToString( <kolom> )

Deze functie zorgt ervoor dat de uitvoer van een kolom altijd eerst wordt omgezet naar een string (lees: tekst).

 

 

sqlToTime( <kolom:datetime>)

In sommige gevallen is het gewenste alleen het tijdstip van een datum weer te geven in een lijstweergave. Deze functie converteert een datum/tijd veld naar een tijdstip zonder datum-aanduiding.

 

 

sqlToVarchar( <kolom:text> )

In SQL Server is het zoeken in of sorteren op een kolom van het type Text niet zonder meer mogelijk. Hiervoor moet de kolom eerst worden geconverteerd naar een standaard Varchar. Deze functie verzorgt deze conversie tijdens het genereren van de query.

 

 

sqlTrim( <expressie:varchar> )

In bepaalde situaties worden meerdere kolomwaarden samengevoegd, waarbij het eindresultaat voor of na de expressie spaties bevat. Door het gebruik van de functie SqlTrim() is het mogelijk de overtollige spaties voor en achter het resultaat te verwijderen.

 

 

Variabelen

Naast de standaard sql* Functies beschikt Logicworks CRM ook over extra variabelen, betreffende de actieve gebruiker, welke in de Query Builder kunnen worden toegepast. Deze variabelen dienen omsloten te worden met accolades en te beginnen met 'user.'. De actuele waarde wordt tijdens het uitvoeren van de query bepaald en in de query opgenomen. Bij het gebruik van deze variabelen dient de technische veldbenaming te worden gebruikt, zoals {user.userid}. U kunt deze technische veldbenamingen opvragen via het tabblad Velden in de Object Designer.

 

Enkele voorbeelden:

 

{user.userid}

Tijdens het opbouwen van het SQL-statement wordt deze variabele vervangen door de  gebruikerscode van de ingelogde gebruiker.

 

{user.delegates}

Tijdens het opbouwen van het SQL-statment wordt deze variabele vervangen door een lijst met de code van alle gebruikers waarvan de ingelogde gebruiker de manager is.

 

{user.teams}

Tijdens het opbouwen van het SQL-statement wordt deze variabele vervangen door een lijst met teams waarin de ingelogde gebruiker deelneemt.

 

{user.businessunit}

Tijdens het opbouwen van het SQL-statement wordt deze variabele vervangen door de werkmaatschappij waartoe de de ingelogde gebruiker behoort. Deze instelling kan worden gezet via de Voorkeurinstellingen van de gebruiker.