Welcome Guest! Log in

Topic-icon Solved Dynamic syntax for Integration table

More
16 Feb 2016 16:55 - 16 Feb 2016 17:05 #1 by Josserand BRINON
Dynamic syntax for Integration table was created by Josserand BRINON
Hi,

We are developing reject conditions in our integration flow.
In one of them, I need to refer to the integration table (generally called "I_<myTargetTable>"), but I don't know how to do that.

The following syntax works for the target table :
%x{md:objectPath(ref:schema('<myDataSchemaName>'), '<myTargetTable>')}x%
--> compiled result: myDatabase.mySchema.myTargetTable
but I need to do the same to retrieve the "I_" table
--> compiled result: myDatabase.myWorkSchemaName.I_myTargetTable

Any idea please? :dry:
Thanks.
Last edit: 16 Feb 2016 17:05 by Josserand BRINON.

Please Log in or Create an account to join the conversation.

More
19 Feb 2016 14:51 #2 by Thomas BLETON
Replied by Thomas BLETON on topic Dynamic syntax for Integration table
Hello,
Can you please provide more details on your development ?
Are you managing exclusions in the mapping's joins ? or filter ?
Are you setting Check Keys on the target's metadata ?

Maybe a screenshot of your mapping / process will help to understand the context ;-)

Please Log in or Create an account to join the conversation.

More
19 Feb 2016 16:40 #3 by Josserand BRINON
Replied by Josserand BRINON on topic Dynamic syntax for Integration table
Here is a screenshot of the mapping:



Very simple, but in SCD mode (it doesn't matter for the actual problem).

And the SQL code of the condition "ERR01_LIEN_TIERS" :
(Good luck :sick: )
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

)
The user message is: "Pour un type de lien, deux tiers ne peuvent être liés qu'une seule fois sur une même date"
It can help to understand...

Techno : SQL Server 2012
Target schema: dbo
Target work schema: stb_work
Attachments:

Please Log in or Create an account to join the conversation.

More
19 Feb 2016 17:00 #4 by Josserand BRINON
Replied by Josserand BRINON on topic Dynamic syntax for Integration table
My condition controls a group of records, with consistent begin date and end date.

A good group can be like that :
1st record -> begin : 2014-03-15 / end : 2014-12-31
2nd record -> begin : 2015-01-01 / end : 2015-01-31
3rd record -> begin : 2015-10-01 / end : null
No matter if there is a gap between the 2nd and the 3rd record.

A reject group can be like that :
1st record -> begin : 2014-03-15 / end : 2015-01-01
2nd record -> begin : 2015-01-01 / end : 2015-01-31
3rd record -> begin : 2015-09-01 / end : null
or like that
1st record -> begin : 2014-03-15 / end : 2014-12-31
2nd record -> begin : 2015-01-01 / end : null
3rd record -> begin : 2015-09-01 / end : null

Please Log in or Create an account to join the conversation.

More
23 Feb 2016 12:05 - 23 Feb 2016 12:05 #5 by Thomas BLETON
Replied by Thomas BLETON on topic Dynamic syntax for Integration table
Thanks for the details.
I think this syntax can do the job :
FROM %x{md:objectPath(../.., ../ref:target()/mdj:pattern(mdj:xpath(.,'INTEGRATION_MASK')))}x%
Last edit: 23 Feb 2016 12:05 by Thomas BLETON.

Please Log in or Create an account to join the conversation.

More
24 Feb 2016 17:10 #6 by Josserand BRINON
Replied by Josserand BRINON on topic Dynamic syntax for Integration table
Thank you Thomas.
It's better but not exactly the result I except:

I expect that:
--> compiled result: myDatabase.myWorkSchemaName.I_myTargetTable
And the solution you suggest do that:
--> compiled result: myDatabase.mySchema.I_myTargetTable
It doesn't work because the "I" table is generated on the Work Schema, not on the target schema.

Please Log in or Create an account to join the conversation.

More
25 Feb 2016 15:20 - 25 Feb 2016 15:22 #7 by Thomas BLETON
Replied by Thomas BLETON on topic Dynamic syntax for Integration table
Oops I missed that point.
Maybe this will do :
FROM %x{md:objectPath(../../ref:work(), ../ref:target()/mdj:pattern(mdj:xpath(.,'INTEGRATION_MASK')))}x%

A few words on this expression :
- The "md:objectPath" function builds an object path (well named ;-)), the first argument being in this case a reference to the desired schema, and the second argument being the table name.
- First argument "../../ref:work()" is evaluated relatively to the CK => the parent of the parent of the CK is the schema, on which we now get the reference to the work schema.
- Second argument "../ref:target()/..." refers to the CK's parent => the table, and applies a replacement pattern to its integrationMask.

This can seem a bit obscure and magical. This kind of advanced usage is usually covered in the advanced training sessions - and YES it IS magical ;-)

Please let me know if this works fine.
Last edit: 25 Feb 2016 15:22 by Thomas BLETON.

Please Log in or Create an account to join the conversation.

More
08 Mar 2016 17:00 #8 by Josserand BRINON
Replied by Josserand BRINON on topic Dynamic syntax for Integration table
Perfect! Thank you Thomas B)
Thanks also for the explanations about the expression. It helps to consider that not so much magical ;)

Are there some documentations about those different functions, or even a simple list of all the available functions?

Please Log in or Create an account to join the conversation.

More
10 Mar 2016 10:42 - 10 Mar 2016 10:47 #9 by Emmanuel Rambeau
Replied by Emmanuel Rambeau on topic Dynamic syntax for Integration table
Hi,

You can find the definition of the functions in the stambiaRuntime\build\xsl\md.xsl. You can also do some retro-engineering.
But I don't know if a documentation about it is available. As Thomas said, it's explained in advanced training session.

Have a nice day.
Last edit: 10 Mar 2016 10:47 by Emmanuel Rambeau.

Please Log in or Create an account to join the conversation.