Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.
The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…
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.