Due to some maintenance operations, stambia.org will be switched to read-only mode during the 13th November. It will be possible to read and download, but impossible to post on the forums or create new accounts. For any question please contact the support team.
Solved
Dynamic syntax for Integration table
%x{md:objectPath(ref:schema('<myDataSchemaName>'), '<myTargetTable>')}x%
--> compiled result: myDatabase.mySchema.myTargetTable--> compiled result: myDatabase.myWorkSchemaName.I_myTargetTable
)EXISTS (
SELECT fk_tiers_parent, fk_tiers_enfant, fk_type_lien_tiers, SUM(fl_incoherence)
FROM (
SELECT
fk_tiers_parent
, fk_tiers_enfant
, fk_type_lien_tiers
/* si la date de début de l'enregistrement suivant est inférieure ou égale à la date de fin de l'enregistrement courant */
, CASE WHEN
CONVERT(INT,
ISNULL(
LEAD(date_debut_lien_tiers, 1) OVER (PARTITION BY fk_tiers_parent, fk_tiers_enfant, fk_type_lien_tiers ORDER BY date_debut_lien_tiers)
, CONVERT(DATETIME, '21991231', 112)+1
)
- date_debut_lien_tiers
)
<=
CONVERT(INT,
ISNULL(
date_fin_lien_tiers,
CONVERT(DATETIME, '21991231', 112)
)
-date_debut_lien_tiers
)
THEN 1 ELSE 0 END fl_incoherence
FROM stb_work.I_lien_tiers /* <---------------------- I want to replace the target work schema by the dynamic syntax */
) lt_ko
WHERE lt_ko.fk_tiers_parent = lien_tiers.fk_tiers_parent
AND lt_ko.fk_tiers_enfant = lien_tiers.fk_tiers_enfant
AND lt_ko.fk_type_lien_tiers = lien_tiers.fk_type_lien_tiers
GROUP BY lt_ko.fk_tiers_parent, lt_ko.fk_tiers_enfant, lt_ko.fk_type_lien_tiers
HAVING SUM(fl_incoherence) = 0
)FROM %x{md:objectPath(../../ref:work(), ../ref:target()/mdj:pattern(mdj:xpath(.,'INTEGRATION_MASK')))}x%), the first argument being in this case a reference to the desired schema, and the second argument being the table name.