Jointures et clauses Where auto-résolues (412)

Depuis l’origine, les clauses WHERE ou JOIN (dans le recordSql DHSQ ou via le dictionnaire de jointures DHSJ) ont parfois besoin de faire un CASE WHEN sur un champ provenant d’un enregistrement public pour déterminer s’il faut tester l’une ou l’autre condition.

Une nouvelle instruction SWITCHSQL (+CASESQL et DEFAULTSQL et ENDSWITCHSQL) permettent d’améliorer le traitement de ces cas

Consultez aussi l’aide en ligne XWIN dans le chapitre RecordSql / Dictionnaire de jointures

Clause Where avec SwitchSql

Exemple de clause where qui a besoin de connaître la valeur de T000.TabTyp(25) pour savoir s’il faut tester #DOSCOMMUN ou MZ.Dos

ECRITURE DANS LE DHSQ OU DHSJ
<WHERE>
	T025.Dos = Case WHEN T000.TabTyp(25) = 2 THEN #DOSCOMMUN ELSE MZ.Dos END

Cette écriture fonctionne, mais a pour inconvénient d’envoyer au serveur SQL cette clause dans laquelle le T000 sera substitué par sa valeur

CE QUI EST ENVOYE AU SERVEUR SQL AVANT
WHERE T025.Dos = Case WHEN 2 = 2 THEN 999 ELSE MZ.Dos END

La nouvelle instruction SWITCHSQL (et CASESQL et DEFAULTSQL) permet de déporter la résolution dans la couche RecordSql avec un écriture différentes, puisqu’on va distinguer les 2 cas (donc 2 where différents) et la condition pour choisir, plutôt que de laisser le choix à l’intérieur de la clause

NOUVELLE ECRITURE
<WHERE>
	SwitchSql T000.TabTyp(25)
		CaseSql 2
			T025.Dos = #DOSCOMMUN
		DefaultSql
			T025.Dos = MZ.Dos
	EndSwitchSql

Cette écriture va permet au RecordSql de teste T000.tabTyp(25) et donc de simplifier ce qui est envoyé au serveur SQL puisqu’il n’y a plus le CASE WHEN

A noter qu’en plus, dans ce cas, l’ajout du .CEBIN au passage permet d'améliorer les performances SQL en guidant mieux le moteur sur les tables communes

Jointure JOIN avec SwitchSql

Le mécanisme décrit précédemment sur une clause WHERE s’applique aussi aux jointures.

Exemple de jointure du dictionnaire de jointures qui a besoin de connaître la valeur de T000.TabTyp(19) pour savoir s’il faut tester #DOSCOMMUN ou celle du from, et en une variante avec celle du MZ.Dos

Le principe est le même que la clause Where.

La nouvelle instruction SWITCHSQL (et CASESQL et DEFAULTSQL) permet de déporter la résolution dans la couche RecordSql avec un écriture différentes, puisqu’on va distinguer les 2 cas (donc 2 ON JOIN différents) et la condition pour choisir, plutôt que de laisser le choix à l’intérieur du ON

Bien que plus verbeux à l'écriture, on distingue mieux les cas d’usage et on réduit le travail du moteur SQL puisque dans cet exemple (simplifié) on économise un case when

Jointures dynamiques (412)

Ce qui suit est une extension des principes du chapitre précédent. Suivez le chapitre Evolution de l'écriture des jointures et clauses where (412) | Clause Where avec SwitchSql avant pour comprendre, et suivez les chapitres dans l’ordre avec l’illustration par l’exemple.

1. Mode d'écriture sans jointure dynamique

Prenons un exemple : la sous-table MARCHE (T034) qui est pointée sur un MOUVement de pièce

1.1 Dans le dictionnaire de jointures

Une jointure MARCHE existe

Elle fait le lien avec les champs du $from mais utilise un CASE WHEN pour résoudre la table commune T034

1.2 Dans le recordSql

Le recordSql du MOUVement fait appel au dictionnaire en utilisant simplement le nom du dictionnaire MARCHE

On a donc le lien entre le $from = MOUV, et T000.TabTyp(34) sera substitué avant envoi de la requête

1.3 Dans le zoom

Dans le zoom du mouvement, rien ; le moteur de zoom se charge d’activer la colonne et la jointure associée

2. Mode d'écriture avec jointure dynamique (.ACTIVATECASE)

L’objectif des jointures dynamiques est de réduire le travail du moteur SQL en lui réduisant les résolutions qui peuvent être faites d’avance.

Dans le dictionnaire de jointures

Pour des raisons d’optimisation, la transformation d’un jointure avec CASE WHEN s’oriente vers 2 écritures différentes.

La première (ECRITURE 1) rejoint le chapitre Evolution de l'écriture des jointures et clauses where (412) | Jointure JOIN avec SwitchSql en introduisant le SWITCHSQL en remplacement du CASE WHEN
La seconde (ECRITURE 2) est une nouveauté, rendue possible par une instruction .ACTIVATECASE sur les jointures à positionner en DIVA, qui permet de réduire encore la requête SQL puisque le CASE WHEN qui était dans la requête SQL passe dans le code DIVA pour choisir une jointure ou une autre

Le choix de ECRITURE 1 ou ECRITURE 2 se fera selon les contraintes et besoins d’optimisation

ECRITURE 1

On utilise les nouvelles instructions SWITCHSQL (et CASESQL et DEFAULTSQL) pour distinguer les cas, ici #DOSCOMMUN et $from.Dos. La jointure qui utilisait une CASE WHEN sur une ligne devient donc un éclatement des CASE en CASESQL

ECRITURE 2

Un autre possibilité existe : elle consiste à distinguer plus simplement le cas #DOSCOMMUN et #MZ.DOS, car par rapport au cas précédent, le traitement du MZ.Dos devient pour le moteur SQL une constante alors que $from.dos est variable.

Donc on sépare dans ce cas en 2 jointures dans le dictionnaire de jointures, l’une sur MZ.Dos et l’autre sur #DOSCOMMUN

Cette seconde écriture a un impact sur le code DIVA qui devra choisir d’activer l’une ou l’autre des jointures

ECRITURE 1 Dans le recordSql et le zoom

Le recordSql du MOUVement fait appel au dictionnaire en utilisant simplement le nom du dictionnaire MARCHE.

Aucune différence d'écriture avec la version préalable, c’est le SWITCHSQL qui traitera la variante selon que la table est commune ou non

Dans le zoom du mouvement, aucune différence d'écriture avec la version préalable

ECRITURE 2 Dans le recordSql et le zoom

Le recordSql du MOUVement est modifié, car il utilise une nouvelle écriture qui permet de décrire des CASE à l’intérieur d’une jointure.

On passe donc dans notre exemple d’un jointure à 1 jointure mais qui fait appel à 2 jointures du dictionnaire de jointures en ajoutant un CASE devant.

Attention, c’est bien 1 jointure pour le RecordSql, mais qui permet un choix entre 2 jointures du dictionnaire en donnant une possibilité de variante (choix final qui est fait en DIVA et non en SQL)

Cette écriture permet d’alléger encore la requête qui sera envoyée au moteur SQL, mais impose de faire le choix (entre les 2 variantes de jointures) dans le code DIVA

Il faut donc, par exemple ici dans le GTTZ420_sql.dhsp qui correspond au zoom des mouvements, ajouter l’activation de l’une ou l’autre variante.

Cela se fait par une nouvelle instruction JOIN.ACTIVATECASE(NomDeLaJointure, NomDuCaseVariantAActiver)

REMARQUE MIGRATION V10.12 : en migration vers la 10.12, on peut constater que les deux écritures ont souvent été mises en oeuvre, et que l’ECRITURE 2 a été principalement utilisée

AVANT	APRES (10.12)

AVANT

APRES (10.12)

Extrait dictionnaire de jointures T034 - MARCHE

Extrait recordSql

1 jointures dans le dictionnaire, longue

4 jointures dans le dictionnaire, courtes

Utilisation de CASE dans les jointures, mais avec utilisation du ACTIVATECASE en Diva

1 Jointures et clauses Where auto-résolues (412)
- 1.1 Clause Where avec SwitchSql
- 1.2 Jointure JOIN avec SwitchSql
2 Jointures dynamiques (412)
- 2.1 1. Mode d'écriture sans jointure dynamique
  - 2.1.1 1.1 Dans le dictionnaire de jointures
  - 2.1.2 1.2 Dans le recordSql
  - 2.1.3 1.3 Dans le zoom
- 2.2 2. Mode d'écriture avec jointure dynamique (.ACTIVATECASE)
  - 2.2.1 Dans le dictionnaire de jointures
    - 2.2.1.1 ECRITURE 1
    - 2.2.1.2 ECRITURE 2
  - 2.2.2 ECRITURE 1 Dans le recordSql et le zoom
  - 2.2.3 ECRITURE 2 Dans le recordSql et le zoom